check and replace $

[alee001]

How to check or replace all refer to address column are assigned (eg. data2, 4) in formula column E (formula1) make sure add with "$" (e.g. $B??, $D??)

e.g.
A B C D E
data1 data2 data3 data4 formula1
53.5 54.25 50.75 52.25 =SUM(B4:E4)/4
47.9 47.9 44.4 45.1 =(B5+E5)/2
45.5 45.5 39 41.9 =E6
43.1 43.5 40.8 41.8 =(C7+D7+E7)/3
45.5 47.2 45 47 =E8/2+B8/2
48.4 49.6 47.5 48.3 =B9
... ... ... ... =various
etc.

[patel45]

Public Sub ToAbsolute()
  Dim Cella As Range
 
  For Each Cella In ActiveCell.SpecialCells(xlCellTypeFormulas, 23)
      If Cella.HasFormula Then
          If Cella.HasArray Then
            Cella.FormulaArray = Application.ConvertFormula( _
              Cella.FormulaArray, _
              FromReferenceStyle:=xlA1, _
              ToReferenceStyle:=xlA1, _
              ToAbsolute:=xlAbsolute)
          Else
            Cella.Formula = Application.ConvertFormula( _
              Cella.Formula, _
              FromReferenceStyle:=xlA1, _
              ToReferenceStyle:=xlA1, _
              ToAbsolute:=xlAbsolute)
          End If
      End If
  Next Cella
 
End Sub

[alee001]

Hi, patel45, thanks for your reference, but I want apply to column B & E only [i.e. address(ColumnAbsolute:=False)] not for all and rows, how to do it? thanks

Public Sub ToAbsolute()
  Dim Cella As Range
 
  For Each Cella In ActiveCell.SpecialCells(xlCellTypeFormulas, 23)
      If Cella.HasFormula Then
          If Cella.HasArray Then
            Cella.FormulaArray = Application.ConvertFormula( _
              Cella.FormulaArray, _
              FromReferenceStyle:=xlA1, _
              ToReferenceStyle:=xlA1, _
              ToAbsolute:=xlAbsolute)
          Else
            Cella.Formula = Application.ConvertFormula( _
              Cella.Formula, _
              FromReferenceStyle:=xlA1, _
              ToReferenceStyle:=xlA1, _
              ToAbsolute:=xlAbsolute)
          End If
      End If
  Next Cella
 
End Sub

[alee001]

I only change the code [ToAbsolute:=xlAbsolute] to [ToAbsolute:=xlRelRowAbsColumn] for all column, but I have not ideal how to set required column(s)??

[alee001]

I only change the code [ToAbsolute:=xlAbsolute] to [ToAbsolute:=xlRelRowAbsColumn] for all column, but I have not ideal how to set required column(s)??

Finally, I solved the problem about absolute required column(s) by following code:

Sub ToAbsoluteCol_B&D()
Dim fomlu As Range
Set fomlu = Range("E2:G100")
For Each fomlu In fomlu.SpecialCells(xlCellTypeFormulas)
    fomlu.Formula = Application.Substitute(fomlu.Formula, "B", "$B")
    fomlu.Formula = Application.Substitute(fomlu.Formula, "D", "$D")
Next fomlu
End Sub