Using created variant value to reference to a variant within my code

[TD]

High everyone,

Love the site, and finally I have question to post!

I wish to make my code more efficient by cycling through a smaller
piece of code as follows:

Sub test()

Dim var As Variant
Dim No As Single
Dim Head1 As String

Head1 = "Test Header 1"

No = 1
While No < 2
var = ("Head" & No)
Range("A1").Offset(0, No).Value = var
No = No + 1
Wend

End Sub

The issue I am getting is the Var is inputting "Head1" instead of
"Test Header 1" into my worksheet.

If anyone can help I'd bre greatly apreciative.

Thanks
Tim

 

[OssieMac]

Hi Tim,

When you assign a string to a variable you do not enclose the variable in
double quotes when you use it in lieu of the actual string. Also I have
adjusted the code to assign only Test Header to the variable so that the
number can be concatenated on the end of it otherwise you will get Test
Header 11 and Test Header 12 etc.


Dim var As Variant
Dim No As Single
Dim Head1 As String

Head1 = "Test Header " 'Note the space at end of string to provide space
before No

No = 1
While No < 2
var = Head1 & No
Range("A1").Offset(0, No).Value = var
No = No + 1
Wend

If you want it to start in column 1 then the following modification.
Subtract 1 from No for the column. Also modified with >= 4 to get 4 headers.

Dim var As Variant
Dim No As Single
Dim Head1 As String

Head1 = "Test Header " 'Note the space at end of string

No = 1
While No <= 4 'Will give 4 headers using <=
var = Head1 & No
Range("A1").Offset(0, No - 1).Value = var 'Subtrace 1 from column
No.
No = No + 1
Wend