Error Using Spaces()
 

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?

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?

Error Using Spaces()
 

Well, the code line-wrapped, so try this version - note you must have the
Option Base 1 to get the array of lengths to work correctly....

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

"Bernie Deitrick" <deitbe @ consumer dot org wrote in message
...
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?

Error Using Spaces()
 

Give this macro a try (change the worksheet name to the actual name of the
worksheet where H1:H8 are located)...

Sub MakeHeader()
Dim X As Long
Dim R As Range
Dim Headers As String
Dim Lengths() As String
Lengths = Split("10 20 10 10 8 12 12 0")
Set R = Worksheets("Sheet1").Range("H1")
For X = 0 To 7
Headers = Headers & Format(R.Offset(X).Value, String(Lengths(X), "@"))
Next
'
' Done... so let's see the results
'
Debug.Print "1234567890123456789012345678901234567890123456789 0" & _
"1234567890123456789012345678901234567890"
Debug.Print Headers
End Sub

--
Rick (MVP - Excel)


"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?