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



In article ,
orquidea wrote:

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

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

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




In article ,
orquidea wrote:

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

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

Orquidea

"JE McGimpsey" wrote:

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




In article ,
orquidea wrote:

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

On Tue, 13 May 2008 11:17:00 -0700, orquidea
wrote:

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

Thanks for your help

"Gord Dibben" wrote:

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

On Tue, 13 May 2008 11:17:00 -0700, orquidea
wrote:

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