FormulaR1C1 code

[PapaSmurf]

I am trying to get a formula into a worksheet which looks like the following;
=IF(ISNA(VLOOKUP(O29,DATABASE!A:S,3,FALSE)),"",VLOOKUP(O29,DATABASE!A:S,3,FALSE))

I have tried this;
'ActiveCell.FormulaR1C1 = "=IF(ISNA(VLOOKUP(RC[-1],DATABASE!R2C1:R65536C19,3,FALSE)),"",VLOOKUP(RC[-1],DATABASE!R2C1:R65536C19,6,FALSE))"

but it doesnt work as the quotes in the middle of the if statement to return a blank cell cause an error. How can I get a blank cell if the ISNA is true ?

THANKS

 

[Richard Schollar]

Try this (haven't tested it, but this is the way to put quotes in the formula):

ActiveCell.FormulaR1C1 = "=IF(ISNA(VLOOKUP(RC[-1],DATABASE!R2C1:R65536C19,3,FALSE))," & CHR(34) & CHR(34) & ",VLOOKUP(RC[-1],DATABASE!R2C1:R65536C19,6,FALSE))"

Best regards

Richard

 

[Travis]

I think R1C1 complicates it. I prefer

ActiveCell.Value = "=IF(ISNA(VLOOKUP(O29,DATABASE!A:S,3,FALSE)),"""",VLOOKUP(O29,DATABASE!A:S,3,FALSE))"

Or I think if you quote the quotes ("""") on yours it would work also

 

[PapaSmurf]

Thanks Parsnip that worked great

the double quotes won't work and i needed the r1c1 format as i need it to be relative.

Any idea how exactly I import a whole database table from access into excell automatically (ie when I run a macro it populates a worksheet so I can run the lookup on it), its not very big and seeing i haven't got a clue about SQL I don't really wont to worry about what i am returning.

 

[Richard Schollar]

The easiest way would be to use Data>Get External Data>New DataBase Query and use MSQuery to pull the information across into excel. You could then code the table to refresh on execution of a macro (eg maybe the WorkBook_Open event).

Post back if you have any trouble.

Regards

Richard