Error Using Spaces()

J

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

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
 
B

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
 
R

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
 
J

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.
 
J

jayklmno

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

Thanks for everyone's help!
 
R

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
 
R

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).
 
D

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top