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
Title | # Comments | Views | Activity |
---|---|---|---|
Excel Need to return the text of a filter column | 11 | 22 | |
Excel 2010 - Selecting and copying cells from areas with hidden rows is not working | 3 | 20 | |
Select/Copy row and pasting it lower in sheet | 7 | 21 | |
If help | 9 | 49 |
Join the community of 500,000 technology professionals and ask your questions.