VLOOKUP Returns #NA
 

On Aug 21, 4:47*am, OssieMac
wrote:
When you have double quotes in the middle of a formula for the null you need
to enclose them in another set of double quotes around them.

* * ActiveCell.FormulaR1C1 = _
* * * * "=IF(ISNA(VLOOKUP(RC[-3],'[GIV
Returns.xls]Sheet1'!C2,1,FALSE)),"""",VLOOKUP(RC[-3],'[GIV
Returns.xls]Sheet1'!C2,1,FALSE))"

You could have done this by recording a macro. You set up the formula on the
worksheet so that it works as you want it to then turn on the macro recorder
and then select the cell with the formula and make a pseudo change like
delete the last bracket and re-insert it then press Enter. Then stop the
macro recorder.

The recorded macro will be in a standard module. (It can be in a newly added
module so if you can't find it then open the other modules in the VBA
Explorer)

--
Regards,

OssieMac



"Sabosis" wrote:
I have that already, the full code I was using was this:

Range("Y3").Select
* * ActiveCell.FormulaR1C1 = _
* * * * "=IF(ISNA(VLOOKUP(RC[-3],'[GIV Returns.xls]Sheet1'!
C2,1,False)),"",VLOOKUP(RC[-3],'[GIV Returns.xls]Sheet1'!C2,1,False))"

The code errors out and the debug key highlights the entire formula, I
don't know what is wrong with it......

On Aug 20, 11:17 pm, OssieMac
wrote:
You need to assign the formula to a cell on the worksheet like this.

ActiveCell.FormulaR1C1 = _
* * * * "=IF(ISNA(VLOOKUP(RC[-3],'[GIV
Returns.xls]Sheet1'!C2,1,False)),"",VLOOKUP(RC[-3],'[GIV
Returns.xls]Sheet1'!C2,1,False))"

--
Regards,

OssieMac

"Sabosis" wrote:
I am trying to get my code to retrun a blank if the match is not made.
I keep getting a VB error and this is highlightd when i debug...

It worked, Thanks! I really appreciate the help!

Scott


=IF(ISNA(VLOOKUP(RC[-3],'[GIV Returns.xls]Sheet1'!
C2,1,False)),"",VLOOKUP(RC[-3],'[GIV Returns.xls]Sheet1'!C2,1,False))

Any ideas?- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -