Macro for division with absolute cell references to replace Indirect formula?

Hi!

I have a workbook where data in Row 4 is continuously and automatically being inserted from another sheet, so that data in Row4 becomes Row5 and fresh data is inserted in Row 4.

I need to divide data of Row 4 by data in Row 13 and get the result in Row 2. I am currently using Indirect formula to keep absolute cell references for Row4/ Row 13.

Please Login or Register  to view this content.

But calculations are being slowed down if I use Indirect formula 300 times i.e. for Col. A to KO, due to the amount of complexity and data coming in continuously.

I was wondering if someone could help me with a macro so that rather than using Indirect formula to keep absolute cell reference of dividing data in row 4 with data in Row 13, I could use a macro and get the result in Row 2.

PS:

Please do remember that I need it for 300 columns and not just for 1 column.

Sample sheet with excel calculation and desired result attached.

Regards,

Naira

Not sure why you are using indirect because it looks like just a regular division.
But for the VBA, try this.
It first gets the formulas then pastes then as values

Please Login or Register  to view this content.

Here is the Application.OnTime method.
And yea the indirect makes sense. It slipped my mind because I have been working with VBA more than worksheet functions lately.
You just have to change "Sheet1" to the sheet you want.

Please Login or Register  to view this content.

Hi Jason,

Almost there, as in, it runs if the Macro is applicable on the sheet it is called from, but gives a Run Time error '1004' Application defined or Object Defined error, and highlights the Rows:

Please Login or Register  to view this content.

if I change the value

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.

and call the code from Sheet1

Seems I have to define a variable but not sure what or how.

If I remove the dot before .Range and change the formula to

Please Login or Register  to view this content.

it goes back to working only on the sheet that is currently active.

Could you please be kind enough to check the sample file I am attaching for you to check with your code.

Regards,

Naira

Ahh I see.
It is because it is using relative references on the formulas.
It has to be on the active sheet.
I guess you could change the formulas to be placed by a loop.
But I have a question for you.

Does it matter if these totals are updated while you are not on the sheet?
Just curious as to what the purpose is.
Because if you are not on the sheet, there would have to be another macro or something that populates theses values.