How can I use variables in formulas in VB?
 

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

How can I use variables in formulas in VB?
 

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

How can I use variables in formulas in VB?
 

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