Edit formula in cell w/ dynamic column reference

[boothrat]

I'm trying to write a macro that will add to the formula already in an active cell. I'm having difficulty getting it to change the column that the formula references. The column always needs to be the one that the cell I'm working with is in, even the column reference to the other sheets. This cell won't always be added to the same row but will always reference the same rows.

Sub Data_Add()

Dim Contents As String


Contents = ActiveCell.Formula
ActiveCell.Formula = Contents & "+((Totals!N$33+Totals!N$34+Totals!N$35)*Data!N$45*0.6)"




End Sub

Thanks

 

[pgc01]

Hi

Use the R1C1 notation, like:

Sub Data_Add()
Dim Contents As String

Contents = ActiveCell.FormulaR1C1
ActiveCell.FormulaR1C1 = Contents & "+((Totals!R33C+Totals!R34C+Totals!R35C)*Data!R45C*0.6)"

End Sub

 

[yky]

Do you mean you want the N in N$33 to be the same column as the activecell? Try this:

Dim Contents As String
Dim ACcolumn As Integer


ACcolumn = ActiveCell.Column
Contents = ActiveCell.Formula

ActiveCell.Formula = Contents & "+((Totals!" & Cells(33, ACcolumn).Address & "+Totals!" _
& Cells(34, ACcolumn).Address & "+Totals!" & Cells(35, ACcolumn).Address _
& ")*Data!" & Cells(45, ACcolumn).Address & "*0.6)"