#REF Error with FormulaR1C1

Hey,

I am getting a reference error when I use the VBA .FormulaR1C1 on a range from I2 to the end of my data. The formula includes a reference for two rows prior.

Please Login or Register  to view this content.

I would understand a reference error in I2, being that it is trying to reference a row prior to 1. It actually goes to the bottom of the workbook, to cell F1048576... Very odd. The problem is the rest of the formulas, which should work fine, produce reference errors?

If I change the to start at row 3, it works just fine. I adjusted the code to put the formula into I2 first, then the rest of the range... so it works.

I would like to know what the issue is or if anyone else has had this problem. Any clue?

Thanks,

Shampoo Monkey

Hi SM,

Two things:

1. How are you calculating EndRow? Do you really have 1 million rows of data? If not, you should find the real last row and stop putting the formula into that row.

2. Do you have calculation set to Automatic or Manual? If it's manual, it may put #REF in all the cells since the first one results in that error. Try changing to Automatic prior to running the macro.

Ah, now I see what you mean about going to the last row..

I can't say I've seen that before, I figured it would just error out on row 2 as you said. Do you need to start in row 2, if it's never going to point to a real cell?

Hey Paul,

To answer your previous question, EndRow is the last used row of data. The formula I posted is just an example of the type of formula I am using. I do need the formula in Row 2 as it handles the instance within the formula.

Really, the formula works just fine, its a bit slapdash because I was in a hurry. The only issue is putting the formula in the range. I have a workaround for it:

Put the formula in the second row first:

Please Login or Register  to view this content.

Then fill in the rest of the rows:

Please Login or Register  to view this content.

Very strange haha. I just wanted to know why it was happening.... Is it a limitation/flaw?

I can't explain it, unfortunately, however to me it seems like a flaw that a formula in row 2 that references a cell 2 rows above it would actually look for a value in the last row of the spreadsheet instead of returning an error.

Hey Paul,

Thanks! I was thinking that, just wasn't sure. Its only the second time I have found something that hasn't worked the way it should in Excel. Mighty good program. = )