Formula problem once inserted by VBA

[sharon_hutchison]

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,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,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

 

[Niek Otten]

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


|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,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,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]

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).

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,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,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]

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

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

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).

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,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,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

 

[sharon_hutchison]

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


and it worked grand. Thank you so much!