View Single Post
  #2   Report Post  
Posted to microsoft.public.excel.programming
Bernie Deitrick Bernie Deitrick is offline
external usenet poster
 
Posts: 5,441
Default 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?