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


--
thorsten
------------------------------------------------------------------------
thorsten's Profile: http://www.excelforum.com/member.php...o&userid=34496
View this thread: http://www.excelforum.com/showthread...hreadid=542619

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


--
mrice

Research Scientist with many years of spreadsheet development experience
------------------------------------------------------------------------
mrice's Profile: http://www.excelforum.com/member.php...o&userid=10931
View this thread: http://www.excelforum.com/showthread...hreadid=542619

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.



thorsten wrote:

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

--
thorsten
------------------------------------------------------------------------
thorsten's Profile: http://www.excelforum.com/member.php...o&userid=34496
View this thread: http://www.excelforum.com/showthread...hreadid=542619

--

Dave Peterson