Hi ChemistB,
maybe :
Sub Test()
Dim DataRange As Range, strSelectionColumn As String, strFormula As String, strComma As String
strComma = ","
'Get DataRange
With Range(Split(ActiveSheet.UsedRange.Address, ":")(1))
Set DataRange = Range(Cells(2, .Column), .Cells(1)).Offset(, 1)
End With
Debug.Print DataRange.Address
'Get the column letter of current selection
strSelectionColumn = Split(Selection.Columns(1).Address, "$")(1)
Debug.Print strSelectionColumn
'This is original formula if typed directly on sheet, but with replace target column with XXX and replace strComma with YYY
strFormula = "=TRIM(MID(TRIM(XXX2),IF(LEFT(TRIM(XXX2),1)=""YYY"",2,1),254))"
Debug.Print strFormula
'Replace the formula with column letter of current selection, then replace with strComma
strFormula = Replace(strFormula, "XXX", strSelectionColumn)
strFormula = Replace(strFormula, "YYY", strComma)
Debug.Print strFormula
'Apply the formula to DataRange
DataRange.Formula = strFormula
End Sub
Bookmarks