Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 - |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
vlookup returns na | Excel Worksheet Functions | |||
vlookup returns more than one value?? | Excel Worksheet Functions | |||
Vlookup returns #N/A | Excel Discussion (Misc queries) | |||
VLookup returns #N/A | Excel Programming | |||
vlookup returns 0.00 | Links and Linking in Excel |