Error Using Spaces()

[jayklmno]

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?

 

[Bernie Deitrick]

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

 

[Bernie Deitrick]

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

 

[Rick Rothstein]

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 "12345678901234567890123456789012345678901234567890" & _
"1234567890123456789012345678901234567890"
Debug.Print Headers
End Sub

 

[jayklmno]

For the sake of code brevity, I shortened the inclusion of the column
headers, the H1 is a string variable, not a cell reference.

H1 = "Sku"
H2 = "Description"
etc.

Sorry for the confusion.

 

[jayklmno]

And I just found my problem, in two of the cases the Space() value had gone
negative.

Thanks for everyone's help!

 

[Rick Rothstein]

Sorry, I just noticed I justified the text to the wrong side within their
field widths. This should do what you want...

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 1")
Set R = Worksheets("Sheet1").Range("H1")
For X = 0 To 6
Headers = Headers & Format(R.Offset(X).Value, _
"!" & String(Lengths(X), "@"))
Next
Headers = Headers & R.Offset(7).Value
'
' Done... so let's see the results
'
Debug.Print "12345678901234567890123456789012345678901234567890" & _
"1234567890123456789012345678901234567890"
Debug.Print Headers
End Sub

 

[Rick Rothstein]

Sorry, a minor error (it doesn't affect functionality, but it might cause
confusion if you try and dissect what the code is doing). In my assignment
to the Lengths array (via the Split function), I accidentally included an
extra value (the 1 at the end)... the loop doesn't read it (which is why it
doesn't affect functionality), but it doesn't represent anything either and
should be removed. The actual statement should read...

Lengths = Split("10 20 10 10 8 12 12")

The number of entries for field widths should be one less than the actual
number of cells being concatenated (as the last cell is concatenated
individually after the loop finishes).

 

[Dana DeLouis]

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

Would this general idea work for you?
I find the following a little easier to debug.

Sub Demo()
Dim s
s = Space(50)
Mid$(s, 1) = "Test"
Mid$(s, 10) = "10"
Mid$(s, 20) = "20"
Debug.Print s

s = Space(50)
Mid$(s, 1) = "Test2"
Mid$(s, 10) = "More"
Mid$(s, 20) = "Junk"
Debug.Print s
End Sub

= = =
HTH :>)
Dana DeLouis