Error Using Spaces()
jay,
To access a cell's value, you need to use, instead of just H1,
Range("H1").Value.
Here is an iterative solution:
Option Base 1
Sub test()
'Headers = H1 & Space(10 - Len(H1)) & H2 & Space(20 - Len(H2)) & H3 &
'Space (10 - Len(H3)) & H4 & Space(10 - Len(H4)) & H5 & Space(8 - Len(H5)) &
H6
'& Space(12 - Len(H6)) & H7 & Space(12 - Len(H7)) & H8
Dim i As Integer
Dim Headers As String
Dim Lengths As Variant
Lengths = Array(10, 20, 10, 10, 8, 12, 12)
Headers = ""
For i = 1 To 8
Headers = Headers & Cells(i, 8).Value
If i < 8 Then Headers = Headers & Space(Lengths(i) - Len(Cells(i,
8).Value))
Next i
MsgBox Headers
End Sub
HTH,
Bernie
MS Excel MVP
"jayklmno" wrote in message
...
I am trying to use the following line of code to produce equally spaced
columns of text.
Headers = H1 & Space(10 - Len(H1)) & H2 & Space(20 - Len(H2)) & H3 &
Space(10 - Len(H3)) & H4 & Space(10 - Len(H4)) & H5 & Space(8 - Len(H5)) &
H6
& Space(12 - Len(H6)) & H7 & Space(12 - Len(H7)) & H8
Headers is a string variable
H1-8 are string column titles
the number after Space( represents the total width I want that column to
be
I am trying to dump data parsed from a query into columns in a text email.
I
want to stay away from HTML.
Why isn't this working or is there another way?
|