Vlookup in VBA

[longhair]

Does anyone know of a way to use vlookup dynamically in a macro? What I'm trying to do is look up info from a cell in a column based upon what is in that cell (much like copying & pasting a cell that already has vlookup in it...
ie.
cell D10 is =vlookup(D10,rangename,3,false)
if I copy & paste to D12 it becomes
=vlookup(D12,rangename,3,false).
I'm really looking for the dynamic part to be the cell address... something like

dim x as int
for x = 1 to 1000
if activecell.text = "N/A" then
activecell.text = "=vlookup(address,rangename,3,false)"
endif
next x

I really would rather not have to move to the range & cycle through all of the rows to see if I get a hit on what was in my "address". Hope this make some sense.
TIA

longhair

 

[RobBroekhuis]

If you need to find the value just once (i.e., at run-time), instead of continue the lookup function as a formula in the Excel sheet, then use
worksheetfunction.vlookup()
in your VBA code, with the same parameters as you would use it in a cell formula. Check back if you need more help to make this work.
Rob

 

[longhair]

Thanks Rob. Am out of the office till Monday. Will try it then.

Regards,

longhair

 

[Zathras]

You wrote:

cell D10 is =vlookup(D10,rangename,3,false)

This is a circular reference. Is that what you intended?

 

[longhair]

All right...figured it out
code as follows, just need to add code to cycle through the entire sheet and to increment counter1.
Sub test1()
Dim y As Integer
Dim PART1 As Range
Dim counter As Integer
Dim counter1 As Integer
Dim astring As String
Dim upc As Double
counter = 0
counter1 = 50
Set PART1 = Worksheets("X&quot.Range("n214875&quot
Do Until counter > 1
counter = counter + 1
astring = ActiveCell.Text
If astring = "#N/A" Then
upc = WorksheetFunction.VLookup((Cells(counter1, 7)), PART1, 2, False)
ActiveCell.Value = upc
End If
Loop
End Sub
Thanks for your point in the right direction Rob.

Regards,

longhair