Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.
Become a Premium Member and unlock a new, free course in leading technologies each month.
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.