cjdhx9
07-22-2008, 01:11 PM
I'm trying to run a simple find and replace macro I made. The part that burns me is that I used it last week and it worked great. Made no changes to the code, opened it and added some info to the sheet, didn't change the code. Now it doesn't work at all. Doesn't pull up an error, it just does nothing. Check it out
Sub FindReplace2()
'Macro is used to replace Cells with "#" in its contents with "N/A"
'Useful for replacing Cells with failed formulas that return "#VALUE" or "#NAME"
Do Until Check = False: Cells.Find(What:="#", After:=ActiveCell, LookIn:=xlValues, LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
, SearchFormat:=False).Activate
' Will Do Until Find Function is False (The key is the "check = False:" command)
' Cells.Find uses several conditions which can be copied from a recorded Find macro
' Default Find setting for LookIn is "xlFormulas", which will look at the true contents of the cell
' For this Find we need to search just the displayed value, to do so we change lookin to equal "xlValues"
' Once the Find function is successful it activates the cell(using .Activate)
' After Activation it continues to next command
ActiveCell.Value = ("N/A")
' Replaces Active Cell with "N/A"
Loop
'Loops Until Find returns False Reading
End Sub
I even made notes for myself so that I could remember how I did everything, and now the darn thing doesn't work. Anyway it's searching the actual visible value of cells for the "#" sign. The only time that occurs in my document is when there is a failed formula. It the replaces that cell with "N/A" and loops until there aren't any "#" Signs left. I have no idea what could be wrong with it.
Sub FindReplace2()
'Macro is used to replace Cells with "#" in its contents with "N/A"
'Useful for replacing Cells with failed formulas that return "#VALUE" or "#NAME"
Do Until Check = False: Cells.Find(What:="#", After:=ActiveCell, LookIn:=xlValues, LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
, SearchFormat:=False).Activate
' Will Do Until Find Function is False (The key is the "check = False:" command)
' Cells.Find uses several conditions which can be copied from a recorded Find macro
' Default Find setting for LookIn is "xlFormulas", which will look at the true contents of the cell
' For this Find we need to search just the displayed value, to do so we change lookin to equal "xlValues"
' Once the Find function is successful it activates the cell(using .Activate)
' After Activation it continues to next command
ActiveCell.Value = ("N/A")
' Replaces Active Cell with "N/A"
Loop
'Loops Until Find returns False Reading
End Sub
I even made notes for myself so that I could remember how I did everything, and now the darn thing doesn't work. Anyway it's searching the actual visible value of cells for the "#" sign. The only time that occurs in my document is when there is a failed formula. It the replaces that cell with "N/A" and loops until there aren't any "#" Signs left. I have no idea what could be wrong with it.