Add formula w/ dynamic column reference

[boothrat]

Sorry, this is a double hit. Looked like my previous post w/ the same question didn't post but I was mistaken

I'm rying to write a macro that adds to the end of the formula in a selected cell. I need the formula to updated to reflect the column that the cell is in. The cell won't always be in the same row but will always reference it's own column, even on the other sheet. (ie: column "N" in the example below needs to change to whatever column the cell is in)

Sub Date_Cbl_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

 

[BQardi]

Something like this:

Sub Date_Cbl_Add()
Dim Contents As String[COLOR=#ff0000], clm As String[/COLOR]
Contents = ActiveCell.Formula
[COLOR=#ff0000]clm = Split(ActiveCell.Address, "$")(1)[/COLOR]
ActiveCell.Formula = Contents & "+((Totals![COLOR=#ff0000]" & clm & "[/COLOR]$33+Totals![COLOR=#ff0000]" & clm & "[/COLOR]$34+Totals![COLOR=#ff0000]" & clm & "[/COLOR]$35)*Data![COLOR=#ff0000]" & clm & "[/COLOR]$45*0.6)"
End Sub

 

[boothrat]

Awesome, worked great.

Thanks

 

[BQardi]

You're welcome