This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.
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
Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.
Have a better answer? Share it in a comment.
If my understanding is correct,,
first you need to do is to make sure that
in your file 'File Path\[2011-4XX.xlsx the sheet 457 Exist and is there.
then you select from the menu Edit you choose Replace and put
in Find
#REF
and in replace
457
and you press replace all
Make sure you save the workbook onto a new name in case you mess up something.
Best for you if it is difficult is to post both workbook and I will hv a look at it.
gowflow