Macro to edit a formula

Hello!

I have a unique problem which I know is going to be child's play for someone else.

I have a monster of a macro, and in one part of it I have it copying the formula from a previous cell. So example would be, P6 is copying O6's formula which is a simple SUM formula. The part I need help on figuring how to edit the formula so it would move over 2 cells.

To explain, P6 =SUM(Z57:Z2256) would be copied to O6, and would need to equal= SUM(AB57:AB2256)

I attempted to use OFFSET, but it would move the O cell and not edit the actual equation.


The reason why I am copying the cell to begin with is that the row number will be different on each Spreadsheet. In the example it is row 57, but in another workbook it would be 41, and so on.


Thank you for any help.

Are you copying from O to P, or P to O? Your question says both.

Also, can you use Q6 to copy the formula to temporarily?

O to P.

And I could use Q6 to copy it temporarily.

In that case you should be able to do something like this:

Please Login or Register  to view this content.

The cell in question would actually be the first empty cell on the 6th row, so how would you put this as the range?

Cells(6, emptyColumn2)

emptyColumn2 = Cells(4, Columns.Count).End(xlToLeft).Column
If emptyColumn2 > 0 Then
emptyColumn2 = emptyColumn2 + 1
End If


I tried
With Range("((Cells(6, emptyColumn2))")

and With Range("(Cells(6, emptyColumn2)")

but neither worked. Is this able to be a range?


Thank you so much for your help.

It would first copy the previous cell's formula into the first empty cell in row 6. (In an example, it would copy O6 to P6 with P6 having been the empty cell). Then the macro would go inside the cell to the left of the new next empty column (P6) to edit the formula.