# FormulaR1C1 code

#### PapaSmurf

##### Board Regular
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

### Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.

#### Richard Schollar

##### MrExcel MVP
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

##### Well-known Member
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

##### Board Regular
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

##### MrExcel MVP
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

Replies
4
Views
403
Replies
3
Views
161
Replies
2
Views
225
Replies
7
Views
611
Replies
10
Views
457

1,195,956
Messages
6,012,538
Members
441,706
Latest member
lizfourcorners

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back