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

G

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
 
B

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
 
N

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
 
G

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
 
N

NickHK

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

NickHK
 
B

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
 
G

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.
 
B

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.
 
G

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
 
G

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.
 
G

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.
 
G

garyusenet

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

Thanks to both of you!

All the best,

Gary
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Grabbing string values from the end of a string of an unknown length 24
return the value of cells and not the formula 3
Insert rows and paste without clearing constants 1
Problem importing a txt file 3
The conversion of a char data type to a datetime data type resulted in an out-of-range datetime valu 0
Getting rid of text in a data set and replacing it with an average ofthe numbers either side of it. 2
Searching in another workbook for a string and returning with the contents of the cell next to it. 1
Outlook crashes with Outlook connector 2002 0

Top