Integrating 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

 

[Guest]

Dim i as long, j as long, k as long, l as long
Dim res as Variant, rng as Range, m as string
Dim n as long, bo as Boolean, res1 as Variant

. . .

set rng = Worksheets("Sheet3").Cells(i,j).Resize(k,l)
res = Application.Vlookup(m,rng,n,bo)
res1 = Application.Match(m,rng.columns(1),bo)

if not iserror(res) then
msgbox "found at" " & rng.Columns(1).Cells(res1,1).Address & _
vbCrLf & " Value is: " & res
else
msgbox "Not found"
End if

 

[Dave Peterson]

You have another response at your other thread.

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