How Run Formula varying the Range

**rcprito** · 07-18-2014, 10:29 AM

Hey guys.

I have a file that varies its range and I have this formula that I want her to run when the file is prepared for it.

Please Login or Register  to view this content.

If I do like this the value from A3 its going to be the same for all the table.

If I remove the $ behind the A3 the same value goes down again. I just would like to say instead of A3 Sheet2.Cells(i + 1, 1) and everything would be OK, but when I do that excel does't recognize it.

can u help me?

Thanks

**JBeaucaire** · 07-19-2014, 04:51 AM

You switch to the .FormulaR1C1 syntax to solve using your looping input method. But in truth, your .Formula method WOULD work if you did not loop. If you entered all your formulas at once into the entire range, then they would adjust themselves going down. For example:

Please Login or Register  to view this content.

Also, Excel 2010 has an IFERROR function you can utilize so as to not have to run the VLOOKUP twice every time:

Please Login or Register  to view this content.

**rcprito** · 07-21-2014, 05:56 AM

Thanks a lot mate.

**JBeaucaire** · 07-21-2014, 10:17 AM

As it appears you've reached a conclusion, I've marked this thread SOLVED for you.
FYI, this is done through the Thread Tools located above the first post in this thread. Thanks.