Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.
Add your voice to the tech community where 5M+ people just like you are talking about what matters.
Sub FixREF()
Dim WS As Worksheet
Dim MaxRow As Long, I As Long, Item As Long
For Each WS In ThisWorkbook.Worksheets
MaxRow = WS.UsedRange.Rows.Count
For I = 1 To MaxRow
If InStr(1, WS.Cells(I, "G").Formula, "#REF") <> 0 Then
Application.DisplayAlerts = False
WS.Cells(I, "G").Formula = Replace(WS.Cells(I, "G").Formula, "#REF", "A" & I)
Application.DisplayAlerts = True
Item = Item + 1
End If
Next I
Next WS
MsgBox ("A total of " & Item & " formula have replaced all '#REF' to their correct values")
End Sub
Sub FixREF()
Dim WS As Worksheet
Dim MaxRow As Long, I As Long, Item As Long
Dim CelFormula
For Each WS In ThisWorkbook.Worksheets
MaxRow = WS.UsedRange.Rows.Count
For Each CelFormula In WS.UsedRange.SpecialCells(xlCellTypeFormulas)
'For I = 1 To MaxRow
If InStr(1, CelFormula.Formula, "#REF") <> 0 Then
'If InStr(1, WS.Cells(I, "G").Formula, "#REF") <> 0 Then
Application.DisplayAlerts = False
CelFormula.Formula = Replace(CelFormula.Formula, "#REF", "A" & CelFormula.Row)
'WS.Cells(I, "G").Formula = Replace(WS.Cells(I, "G").Formula, "#REF", "A" & I)
Application.DisplayAlerts = True
Item = Item + 1
End If
'Next I
Next CelFormula
Next WS
MsgBox ("A total of " & Item & " formula have replaced all '#REF' to their correct values")
End Sub
Sub FixREF()
Dim WS As Worksheet
Dim MaxRow As Long, I As Long, Item As Long
Dim CelFormula
For Each WS In ThisWorkbook.Worksheets
MaxRow = WS.UsedRange.Rows.Count
For Each CelFormula In WS.UsedRange.SpecialCells(xlCellTypeFormulas)
'For I = 1 To MaxRow
If InStr(1, CelFormula.Formula, "#REF") <> 0 Then
'If InStr(1, WS.Cells(I, "G").Formula, "#REF") <> 0 Then
Application.EnableEvents = False
Application.DisplayAlerts = False
On Error Resume Next
CelFormula.Formula = Replace(CelFormula.Formula, "#REF", "A" & CelFormula.Row)
On Error GoTo 0
'WS.Cells(I, "G").Formula = Replace(WS.Cells(I, "G").Formula, "#REF", "A" & I)
Application.DisplayAlerts = True
Application.EnableEvents = True
Item = Item + 1
End If
'Next I
Next CelFormula
Next WS
MsgBox ("A total of " & Item & " formula have replaced all '#REF' to their correct values")
End Sub
Sub FixREF()
Dim WS As Worksheet
Dim MaxRow As Long, I As Long, Item As Long
Dim CelFormula
For Each WS In ThisWorkbook.Worksheets
MaxRow = WS.UsedRange.Rows.Count
For Each CelFormula In WS.UsedRange.SpecialCells(xlCellTypeFormulas)
'For I = 1 To MaxRow
If InStr(1, CelFormula.Formula, "#REF") <> 0 Then
'If InStr(1, WS.Cells(I, "G").Formula, "#REF") <> 0 Then
Application.EnableEvents = False
Application.DisplayAlerts = False
On Error Resume Next
CelFormula.Formula = Replace(CelFormula.Formula, "#REF", WS.Cells(CelFormula.Row, "A").Value)
'CelFormula.Formula = Replace(CelFormula.Formula, "#REF", "A" & CelFormula.Row)
On Error GoTo 0
'WS.Cells(I, "G").Formula = Replace(WS.Cells(I, "G").Formula, "#REF", "A" & I)
Application.DisplayAlerts = True
Application.EnableEvents = True
Item = Item + 1
End If
'Next I
Next CelFormula
Next WS
MsgBox ("A total of " & Item & " formula have replaced all '#REF' to their correct values")
End Sub
If you are experiencing a similar issue, please ask a related question
Join the community of 500,000 technology professionals and ask your questions.