Macro - Vlookup

[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

 

[JE McGimpsey]

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

 

[orquidea]

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) = ""

 

[Gord Dibben]

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

 

[JE McGimpsey]

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(" & ...

 

[orquidea]

You got it. Thanks, it worked with the word "evaluate" You are a life saver.

Orquidea

 

[orquidea]

Thanks for your help

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