Macro - Vlookup

  • Thread starter Thread starter orquidea
  • Start date Start date
O

orquidea

Hi

How should I write the below procedure properly in order for not to get the
error #NAME?

Range("R2").Select

ActiveCell.Value = "=VLOOKUP(ActiveCell.Offset(0,-6),Sheet1!A:B,2,FALSE)"


Thanks,
Orquidea
 
One way:

Range("R2").Select
ActiveCell.Formula = "=VLOOKUP(" & ActiveCell.Offset( _
0, -6).Address(False, False) & ",Sheet1!A:B,2,FALSE)"


or, equivalently:

Range("R2").Select
With ActiveCell
.Formula = "=VLOOKUP(" & .Offset(0, -6).Address(False, False) & _
",Sheet1!A:B,2,FALSE)"
End With
 
Thanks for your answer. I like the first option and tried and it worked.
However I am still having problems when I try it in the following macro.

Thanks a lot for your help.

Range("R2").Select

Do

If ActiveCell.Offset(0, -1) <= "=VLOOKUP(" & ActiveCell.Offset(0,
-6).Address(False, False) & ",Sheet1!A:B,2,FALSE)" Then
ActiveCell.Value = "On Time"
ActiveCell.Offset(1, 0).Select
Else
ActiveCell.Value = "Delayed"

ActiveCell.Offset(1, 0).Select
End If

Loop Until ActiveCell.Offset(0, -14) = ""
 
Dim rng1 As Range
Range("R2").Select
Set rng1 = Application.ActiveCell.Offset(0, -6)
rng1.Name = "myvar"
ActiveCell.Value = "=VLOOKUP(myvar,Sheet1!A:B,2,FALSE)"


Gord Dibben MS Excel MVP
 
Not sure what you're really trying to do here.

In the first iteration of the Do loop


"=VLOOKUP(" & ActiveCell.Offset(0, -6).Address(False, False) &
",Sheet1!A:B,2,FALSE)"

builds the string

"=VLOOKUP(L2,Sheet1!A:B,2,FALSE)"

That string then gets compared ('<=') to whatever value is in

ActiveCell.Offset(0, -1)

or cell Q2.

Are you wanting to evaluate the string to perform the VLOOKUP? If so,
you could use

If ActiveCell.Offset(0, -1).Value <= Evaluate("VLOOKUP(" & ...
 
Thanks for your help

Gord Dibben said:
Dim rng1 As Range
Range("R2").Select
Set rng1 = Application.ActiveCell.Offset(0, -6)
rng1.Name = "myvar"
ActiveCell.Value = "=VLOOKUP(myvar,Sheet1!A:B,2,FALSE)"


Gord Dibben MS Excel MVP
Click to expand...
 
Back
Top