Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
VLOOKUP problem using VBA
hi, I have some code that inserts a VLOOKUP function in my spreadsheet. the only thing is, it always goes to the 'true' part of the statement. here's the code that inserts it: Code: -------------------- Columns("E:E").Select Selection.Insert Shift:=xlToRight lastrow1 = Cells(Rows.Count, "D").End(xlUp).Row Range("E" & lastrow1 & ":E2").Select Selection.FormulaR1C1 = "=IF(ISERROR(VLOOKUP(RC[-1],'PET BDC - Design Phase Dev'!D:D,1,0)), ""False"", ""True"")" -------------------- everything comes back as "False", but if I then delete the column that was inserted, and reinsert manually and type int he formula, it works fine. can anyone tell me why this might not be working... thanks, Matt -- matpj ------------------------------------------------------------------------ matpj's Profile: http://www.excelforum.com/member.php...o&userid=21076 View this thread: http://www.excelforum.com/showthread...hreadid=537145 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
VLOOKUP problem using VBA
Does this give the same
Columns("E:E").Insert Shift:=xlToRight lastrow1 = Cells(Rows.Count, "D").End(xlUp).Row Range("E" & lastrow1 & ":E2").FormulaR1C1 = _ "=NOT(ISNUMBER(MATCH(RC[-1],'PET BDC - Design Phase Dev'!D:D,0)))" -- HTH Bob Phillips (remove nothere from email address if mailing direct) "matpj" wrote in message ... hi, I have some code that inserts a VLOOKUP function in my spreadsheet. the only thing is, it always goes to the 'true' part of the statement. here's the code that inserts it: Code: -------------------- Columns("E:E").Select Selection.Insert Shift:=xlToRight lastrow1 = Cells(Rows.Count, "D").End(xlUp).Row Range("E" & lastrow1 & ":E2").Select Selection.FormulaR1C1 = "=IF(ISERROR(VLOOKUP(RC[-1],'PET BDC - Design Phase Dev'!D:D,1,0)), ""False"", ""True"")" -------------------- everything comes back as "False", but if I then delete the column that was inserted, and reinsert manually and type int he formula, it works fine. can anyone tell me why this might not be working... thanks, Matt -- matpj ------------------------------------------------------------------------ matpj's Profile: http://www.excelforum.com/member.php...o&userid=21076 View this thread: http://www.excelforum.com/showthread...hreadid=537145 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
VLOOKUP problem using VBA
it does indeed! but then when I stop the macro and go into the cell that has the formula in and press enter, it kind of kicks the formula into gear and it works. I have a range of data containing about 50 rows, of which there are about 5 instances where your formula would return false rather than true. when the macro inserts the formula everything is true. If I then go to those ones I KNOW are false and select the cell and then press enter it suddenly changes to "FALSE" the same if I drag the formula down to cover all ranges, all of those false ones suddenly appear.. I don't understand why its doing this. -- matpj ------------------------------------------------------------------------ matpj's Profile: http://www.excelforum.com/member.php...o&userid=21076 View this thread: http://www.excelforum.com/showthread...hreadid=537145 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
VLOOKUP problem using VBA
I'm an idiot. Didn't spot that you had mixed up R1C1 references and A1
refernces in a FormukaR1C1. Use Columns("E:E").Insert Shift:=xlToRight lastrow1 = Cells(Rows.Count, "D").End(xlUp).Row Range("E" & lastrow1 & ":E2").FormulaR1C1 = _ "=NOT(ISNUMBER(MATCH(RC[-1],'PET BDC - Design Phase Dev'!C4,0)))" -- HTH Bob Phillips (remove nothere from email address if mailing direct) "matpj" wrote in message ... it does indeed! but then when I stop the macro and go into the cell that has the formula in and press enter, it kind of kicks the formula into gear and it works. I have a range of data containing about 50 rows, of which there are about 5 instances where your formula would return false rather than true. when the macro inserts the formula everything is true. If I then go to those ones I KNOW are false and select the cell and then press enter it suddenly changes to "FALSE" the same if I drag the formula down to cover all ranges, all of those false ones suddenly appear.. I don't understand why its doing this. -- matpj ------------------------------------------------------------------------ matpj's Profile: http://www.excelforum.com/member.php...o&userid=21076 View this thread: http://www.excelforum.com/showthread...hreadid=537145 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
VLOOKUP problem using VBA
perfect!! although I have other VLOOKUPS I created ages ago which work fine as they are (RC and normal format) oh well. thank you very much for that - it was so frustrating not being able to fix it!! Matt -- matpj ------------------------------------------------------------------------ matpj's Profile: http://www.excelforum.com/member.php...o&userid=21076 View this thread: http://www.excelforum.com/showthread...hreadid=537145 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
vlookup problem | Excel Discussion (Misc queries) | |||
Vlookup problem with Date Time | Excel Worksheet Functions | |||
vlookup Problem | Excel Discussion (Misc queries) | |||
VLOOKUP Problem | Excel Worksheet Functions | |||
VLOOKUP, OFFSET, MATCH PROBLEM, HELP? | Excel Worksheet Functions |