Hi all,

I have the following code:

Sub test2()
Dim j As Integer
Dim varCol3 As Integer, varCol4 As Integer, varCol5 As Integer

varCol3 = 13
varCol4 = 14
varCol5 = 15

For j = 3 To 5
ActiveCell.Offset(0, j).Value = Evaluate("varCol" & Str(j))
Next j
End Sub

Why would evaluate() not evalute what I expect, i.e put th
corresponding values into the cells?

Thanks in advance

--
Message posted from http://www.ExcelForum.com

It looks like you are trying to do the following:

Sub test()
Dim myvariable As String

myvariable = "example string"

MsgBox Evaluate("myvariable")
End Sub


It don't think it's not possible to get the value of a variable using a
string containing it's name.


What you could do is as follows:

Sub test2()
Dim j As Integer
Dim varCol3 As Integer, varCol4 As Integer, varCol5 As Integer

varCol3 = 13
varCol4 = 14
varCol5 = 15

For j = 3 To 5
With ActiveCell.Offset(0, j)
Select Case j
Case 3: .Value = varCol3
Case 4: .Value = varCol4
Case 5: .Value = varCol5
End Select
End With
Next j
End Sub


--
Rob van Gelder - http://www.vangelder.co.nz/excel


"kaon " wrote in message
...
Hi all,

I have the following code:

Sub test2()
Dim j As Integer
Dim varCol3 As Integer, varCol4 As Integer, varCol5 As Integer

varCol3 = 13
varCol4 = 14
varCol5 = 15

For j = 3 To 5
ActiveCell.Offset(0, j).Value = Evaluate("varCol" & Str(j))
Next j
End Sub

Why would evaluate() not evalute what I expect, i.e put the
corresponding values into the cells?

Thanks in advance.


---
Message posted from http://www.ExcelForum.com/

Hi,

The simple answer is that Evaluate doesn't work like that :-(

The Evaluate function permits VBA to do what you might expect to do from an
XL cell. For example if cell A1 held 2 and A2 held 4 then
Evaluate("=A1+A2") would yield 6.

In effect Evaluate "passes back" to XL the calc. Sadly, XL knows nothing
about varCol3, varCol4 etc. as they sit in VBA-land.

Although Evaluate is a cunning function, it is one that is not commonly
used. Perhaps you could describe what you are trying to do?

One solution to your example would be:

Sub Test3()
dim i as integer

for i = 3 to 5
ActiveCell.Offset(0,i).Value = i + 10
next i
End Sub

Regards,

Peter Beach


"kaon " wrote in message
...
Hi all,

I have the following code:

Sub test2()
Dim j As Integer
Dim varCol3 As Integer, varCol4 As Integer, varCol5 As Integer

varCol3 = 13
varCol4 = 14
varCol5 = 15

For j = 3 To 5
ActiveCell.Offset(0, j).Value = Evaluate("varCol" & Str(j))
Next j
End Sub

Why would evaluate() not evalute what I expect, i.e put the
corresponding values into the cells?

Thanks in advance.


---
Message posted from http://www.ExcelForum.com/