Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using created variant value to reference to a variant within my code
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using created variant value to reference to a variant within my co
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 -- Regards, OssieMac "TD" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Variant | Excel Programming | |||
Using found variant within code | Excel Programming | |||
is a variant an object | Excel Programming | |||
Variant | Excel Programming | |||
using find with variant? | Excel Programming |