Counting the length of a string and adding spaces...

[garyusenet]

I'm currently constructing the string using the following code: -

textstring = (Cells(ActiveCell.Row, make).Value) & " - " &
(Cells(ActiveCell.Row, model).Value) & " - " & (Cells(ActiveCell.Row,
expirary).Value) & " - " & "£" & (Cells(ActiveCell.Row,
commision).Value) & Chr(10)

This produces something like the following, eg.

Honda - Civic 1.5 5dr - 10/02/2001 - £674
Honda - Civic Sport 1.4i 5dr Hatch - 24/06/2001 - £400
Renault - Clio 16V 3dr Hatch - 24/06/2001 - £622

But I would like the information to appear as if it was in columns.
More like this: -

Honda - Civic 1.5 5dr - 10/02/2001 - £674
Honda - Civic Sport 1.4i 5dr Hatch - 24/06/2001 - £400
Renault - Clio 16V 3dr Hatch - 24/06/2001 - £622

So I need to figure out how to add spaces between each variable as it
is put into the string, depending upon the size of the variable. I
assume there are functions that let you count the length of a string,
but i'm not sure how I would go about changing the spacing between the
variables upon the result of this.

Any ideas? or Code? it would be greatly appreciated!

Thankyou

 

[Bob Phillips]

With ActiveCell
sMake = Cells(.Row, make).Value
sModel = Cells(.Row, model).Value
sExpiry = Cells(.Row, expirary).Value
sCommission = Cells(.Row, commision).Value
textstring = sMake & Space(20 - Len(sMake)) & " - " & _
sModel & Space(30 - Len(sModel)) & " - " & _
sExpiry & Space(30 - Len(sExpiry)) & " - " & _
"£" & sCommission & Space(30 - Len(sCommission)) &
Chr(10)
End With



--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

I'm currently constructing the string using the following code: -

textstring = (Cells(ActiveCell.Row, make).Value) & " - " &
(Cells(ActiveCell.Row, model).Value) & " - " & (Cells(ActiveCell.Row,
expirary).Value) & " - " & "£" & (Cells(ActiveCell.Row,
commision).Value) & Chr(10)

This produces something like the following, eg.

Honda - Civic 1.5 5dr - 10/02/2001 - £674
Honda - Civic Sport 1.4i 5dr Hatch - 24/06/2001 - £400
Renault - Clio 16V 3dr Hatch - 24/06/2001 - £622

But I would like the information to appear as if it was in columns.
More like this: -

Honda - Civic 1.5 5dr - 10/02/2001 - £674
Honda - Civic Sport 1.4i 5dr Hatch - 24/06/2001 - £400
Renault - Clio 16V 3dr Hatch - 24/06/2001 - £622

So I need to figure out how to add spaces between each variable as it
is put into the string, depending upon the size of the variable. I
assume there are functions that let you count the length of a string,
but i'm not sure how I would go about changing the spacing between the
variables upon the result of this.

Any ideas? or Code? it would be greatly appreciated!

Thankyou

 

[NickHK]

Are you using a fixed-width font for display ?
If not, there's no easy way to get the visual length of the parts.
i.e. the 2 string "WWW" and "iii" are both 3 chars, but considerable
different in their displayed length.

NickHK

I'm currently constructing the string using the following code: -

textstring = (Cells(ActiveCell.Row, make).Value) & " - " &
(Cells(ActiveCell.Row, model).Value) & " - " & (Cells(ActiveCell.Row,
expirary).Value) & " - " & "£" & (Cells(ActiveCell.Row,
commision).Value) & Chr(10)

This produces something like the following, eg.

Honda - Civic 1.5 5dr - 10/02/2001 - £674
Honda - Civic Sport 1.4i 5dr Hatch - 24/06/2001 - £400
Renault - Clio 16V 3dr Hatch - 24/06/2001 - £622

But I would like the information to appear as if it was in columns.
More like this: -

Honda - Civic 1.5 5dr - 10/02/2001 - £674
Honda - Civic Sport 1.4i 5dr Hatch - 24/06/2001 - £400
Renault - Clio 16V 3dr Hatch - 24/06/2001 - £622

So I need to figure out how to add spaces between each variable as it
is put into the string, depending upon the size of the variable. I
assume there are functions that let you count the length of a string,
but i'm not sure how I would go about changing the spacing between the
variables upon the result of this.

Any ideas? or Code? it would be greatly appreciated!

Thankyou

 

[garyusenet]

Thank you Bob that looks ideal, but there is a slight problem at the
moment - when I run the code i get an error that mentions an 'invalid
procedure call or argument'. When I click debug the following code is
highlighted: -

textstring = sMake & Space(20 - Len(sMake)) & " - " & _
sModel & Space(30 - Len(sModel)) & " - " & _
sExpiry & Space(30 - Len(sExpiry)) & " - " & _
"£" & sCommission & Space(30 - Len(sCommission)) &
Chr(10)


Thanks,

Gary

 

[garyusenet]

On the fixed width - how do i use a fixed width font?

Thanks,

Gary

 

[NickHK]

Set the font of whatever you are using to display this string to something
like "Courier New".

NickHK

 

[Bob Phillips]

See if this helps

With ActiveCell
sMake = Cells(.Row, make).Value
sModel = Cells(.Row, model).Value
sExpiry = Cells(.Row, expirary).Value
sCommission = Cells(.Row, commision).Value
textstring = sMake & Space(20 - Len(sMake)) & " - " & _
sModel & Space(30 - Len(sModel)) & " - " & _
sExpiry & Space(30 - Len(sExpiry)) & " - " & _
"£" & sCommission & Space(30 - Len(sCommission)) & _
Chr(10)
End With



--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

Thank you Bob that looks ideal, but there is a slight problem at the
moment - when I run the code i get an error that mentions an 'invalid
procedure call or argument'. When I click debug the following code is
highlighted: -

textstring = sMake & Space(20 - Len(sMake)) & " - " & _
sModel & Space(30 - Len(sModel)) & " - " & _
sExpiry & Space(30 - Len(sExpiry)) & " - " & _
"£" & sCommission & Space(30 - Len(sCommission)) &
Chr(10)


Thanks,

Gary

 

[garyusenet]

Hi Bob,

That is giving the same error, i've checked and I think that code is
identical to the first code you posted.

Thanks again,

Gary.

 

[Bob Phillips]

It is, but I thought it might be word-wrap, so I split a line.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

Hi Bob,

That is giving the same error, i've checked and I think that code is
identical to the first code you posted.

Thanks again,

Gary.

 

[garyusenet]

Dear Bob I have noticed that it is only generating the error on certain
rows, if i run it on a row it will work fine, but then if i run it on
some other row it generates the problem.
I've included the full code for the macro i'm using here, which the
code you gave me was a part of. Could you please take a look and see
where you think it might be going wrong?

.....

Sub SelectEveryThird()
Dim ilastcolumn As Long
Dim textstring As String

Application.ScreenUpdating = False

ilastcolumn = (Cells(ActiveCell.Row,
Columns.Count).End(xlToLeft).Column)

' 42 is the number of columns between repeated fields.
' 6 is the numberical reference for the first column that contains make
data.

model = 7
expirary = 38
commision = 42

For make = 6 To ilastcolumn Step 42

With ActiveCell
sMake = Cells(.Row, make).Value
sModel = Cells(.Row, model).Value
sExpiry = Cells(.Row, expirary).Value
sCommission = Cells(.Row, commision).Value
textstring = sMake & Space(20 - Len(sMake)) & " - " & _
sModel & Space(30 - Len(sModel)) & " - " & _
sExpiry & Space(30 - Len(sExpiry)) & " - " & _
"£" & sCommission & Space(30 - Len(sCommission)) &
_
Chr(10)
End With


If model + 42 >= ilastcolumn Then
model = ilastcolumn
Else
model = model + 42
End If

If expirary + 42 >= ilastcolumn Then
k = expirary
Else
expirary = expirary + 42
End If

If commision + 42 >= ilastcolumn Then
commision = ilastcolumn
Else
commision = commision + 42
End If

Next make

Cells(ActiveCell.Row, "A").Value = textstring
End Sub

 

[garyusenet]

The only thing to note is that the textstring= in the code I use starts
textstring= textring & .. because i'm compiling a list of order data.

 

[garyusenet]

I have figured out the problem, the 20, and 30 for the spaces were not
long enough so were generating negative spacing for high value strings.
Having fixed this i have one final hurdle.

The above code is not creating a list of data, instead it is creating
only one line of data into Cell A.

I had though that by having textstring = textstring & ...

that the textstring would increase by the size of the newest textstring
at each interation but for some reason i'm only getting one line of
data, regardless of the number of columns?

Many Thanks,

Gary.

 

[garyusenet]

Sorted. I had misspelled the variable.
It's working like a gem now.

Thanks to both of you!

All the best,

Gary