How can I use variables in formulas in VB?

[thorsten]

Hello,
I would like to integrate variables in formulas in Visual Basic, but I
don't now how to do.

My current problem is that I have to use the VLOOKUP-command in a loop,
but I can't find the solution how to define the "table_array"-entry as a
range that is changing.

So, how can I define a range (where I include my varibles) and what
command do I need to use this range in my formula?
Please help me...


Thanks a lot,

Thorsten

 

[mrice]

http://www.excelforum.com/showthread.php?t=542637 might help.

 

[Dave Peterson]

Untested....

Option Explicit
Sub Testme()
Dim myRng as range
dim myCell as range
dim myTable as range
dim res as variant

with worksheets("Sheet99")
set myrng = .range("a2",.cells(.rows.count,"A").end(xlup))
end with

with worksheets("sheet101010")
set myTable = .range("a:E")
end with

for each mycell in myrng.cells
res = application.vlookup(mycell.value,mytable,2,false)
if iserror(res) then
'like #n/a in excel
mycell.offset(0,3).value = "Missing"
else
mycell.offset(0,3).value = "Found: " & res
end if
next mycell
end sub

Sometimes it's easier to just plop the =vlookup()'s in the cells you want and
then convert them to values--not doing any of the =vlookup() in code.