Formula problem once inserted by VBA
 

I have the following line which inserts a vlookup formula into a cell,
drops down to the next cell does the same in a loop....

ActiveCell.Formula = "=VLOOKUP(RC[-1],Delivery!B:D,2,false)"

However, all I get is a #NAME? error, but if I click on the cell to
edit it and hit return the formula magically works! How do I get the
formula to work without having to go into every cell once I've run my
macro?

Also, if I type the vlookup into another cell
=vlookup(A2,Delivery!B:D,2,false) and go between the cells the formula
is EXACTLY the same, yet one (the manually entered one) works and the
other (entered by macro) doesn't until I've entered it and hit
return......

I'm stumped.

Thanks

Formula problem once inserted by VBA
 

You have a mixture of the 2 reference styles: R1C1 and A1
Probably easiest to change the RC[-1] to A1

--
Kind regards,

Niek Otten
Microsoft MVP - Excel


wrote in message ups.com...
|I have the following line which inserts a vlookup formula into a cell,
| drops down to the next cell does the same in a loop....
|
| ActiveCell.Formula = "=VLOOKUP(RC[-1],Delivery!B:D,2,false)"
|
| However, all I get is a #NAME? error, but if I click on the cell to
| edit it and hit return the formula magically works! How do I get the
| formula to work without having to go into every cell once I've run my
| macro?
|
| Also, if I type the vlookup into another cell
| =vlookup(A2,Delivery!B:D,2,false) and go between the cells the formula
| is EXACTLY the same, yet one (the manually entered one) works and the
| other (entered by macro) doesn't until I've entered it and hit
| return......
|
| I'm stumped.
|
| Thanks
|

Formula problem once inserted by VBA
 

Since your formula relies on the activecell, it would make it easier to
translate your rc[-1] if you shared the activecell's location. Niek guessed
that the formula was going into B1 (so rc[-1] refereed to A1.

Another option would be:

ActiveCell.Formular1c1 = "=VLOOKUP(RC[-1],Delivery!c2:c4,2,false)"

Since this uses .formular1c1, delivery!c2:c4 refers to Column 2 to Column 4 in
R1C1 notation (same as B:D).





wrote:

I have the following line which inserts a vlookup formula into a cell,
drops down to the next cell does the same in a loop....

ActiveCell.Formula = "=VLOOKUP(RC[-1],Delivery!B:D,2,false)"

However, all I get is a #NAME? error, but if I click on the cell to
edit it and hit return the formula magically works! How do I get the
formula to work without having to go into every cell once I've run my
macro?

Also, if I type the vlookup into another cell
=vlookup(A2,Delivery!B:D,2,false) and go between the cells the formula
is EXACTLY the same, yet one (the manually entered one) works and the
other (entered by macro) doesn't until I've entered it and hit
return......

I'm stumped.

Thanks

--

Dave Peterson

Formula problem once inserted by VBA
 

(so rc[-1] referred to A1).

There were no referees harmed in the making of that post!

Dave Peterson wrote:

Since your formula relies on the activecell, it would make it easier to
translate your rc[-1] if you shared the activecell's location. Niek guessed
that the formula was going into B1 (so rc[-1] refereed to A1.

Another option would be:

ActiveCell.Formular1c1 = "=VLOOKUP(RC[-1],Delivery!c2:c4,2,false)"

Since this uses .formular1c1, delivery!c2:c4 refers to Column 2 to Column 4 in
R1C1 notation (same as B:D).

wrote:

I have the following line which inserts a vlookup formula into a cell,
drops down to the next cell does the same in a loop....

ActiveCell.Formula = "=VLOOKUP(RC[-1],Delivery!B:D,2,false)"

However, all I get is a #NAME? error, but if I click on the cell to
edit it and hit return the formula magically works! How do I get the
formula to work without having to go into every cell once I've run my
macro?

Also, if I type the vlookup into another cell
=vlookup(A2,Delivery!B:D,2,false) and go between the cells the formula
is EXACTLY the same, yet one (the manually entered one) works and the
other (entered by macro) doesn't until I've entered it and hit
return......

I'm stumped.

Thanks

--

Dave Peterson

--

Dave Peterson

Formula problem once inserted by VBA
 

I changed it to ActiveCell.Formular1c1 =
"=VLOOKUP(RC[-1],Delivery!c2:c4,2,false)"


and it worked grand. Thank you so much!