VLOOKUP problem using VBA

M

matpj

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
 
B

Bob Phillips

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 said:
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
Click to expand...
Phase Dev'!D:D,1,0)), ""False"", ""True"")"
 
M

matpj

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. :confused:
 
B

Bob Phillips

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

matpj

perfect!!

although I have other VLOOKUPS I created ages ago which work fine as
they are (RC and normal format) :confused:
oh well.

thank you very much for that - it was so frustrating not being able to
fix it!!

Matt
 
Top