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

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)

wrote in message
oups.com...
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

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

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)

wrote in message
ups.com...
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

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 wrote:
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)

wrote in message
ups.com...
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

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)

wrote in message
ups.com...
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 wrote:
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)

wrote in message
ups.com...
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

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

wrote in message
oups.com...
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

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

Thanks,

Gary

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

NickHK

wrote in message
oups.com...
On the fixed width - how do i use a fixed width font?

Thanks,

Gary