Find replace issues!! Help!!

[AbeLincolnVampSlayer]

Okay so I have massive spreadsheets that have iferror statements in them to eliminate N/A errors in my sheet. Certain information shifts to different dates on different months. I used to do a find replace all in 2003 and I would hold down escape whenever it hit errors for the day that was missing info for that month. Now in 2010 as soon as I hit an error the search stops.

I am dealing with 3200 cells and 1/4th of them will error depending on what day is missing info.

Advice please

[macropod]

Without the code and some sample data (including data you're having problems with), you can't realistically expect anyone to be able to help.

[AbeLincolnVampSlayer]

Okay here is my data where month directory is \0412 in all cells I want to replace all with \0512.

So I do a find replace all to have it cycle through. When there is an error in 2003 excell keeps looking you can hold down escape and it will go through the whole thing ignoring the errors.

In excell 2010 it stops as soon as it finds missing data. With 3000 plus cells per sheet this would take me all day to restart the find replace every time data is missing for a day.

[macropod]

Try the following macro:

Sub UpdateRefs()
Dim StrFnd As String, StrRep As String
StrFnd = InputBox("Please input the string to FIND for the replacement.", "Find/Replace")
If StrFnd = "" Then Exit Sub
StrRep = InputBox("Please input the string with which to REPLACE the Find String.", "Find/Replace")
With Application
  .DisplayAlerts = False
  .ScreenUpdating = False
  .Calculation = xlManual
  .ActiveSheet.Cells.Replace What:=StrFnd, Replacement:=StrRep, LookAt:=xlPart, _
    SearchOrder:=xlByColumns, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
  .Calculation = xlAutomatic
  .DisplayAlerts = True
  .ScreenUpdating = True
End With
End Sub

[AbeLincolnVampSlayer]

Wow I have a feeling I am behind the times on doing stuff the easy way in excell. I do not know a lot about macros, is there any book or training you could recomend that would get me up to speed?

Thanks a lot!!

Originally Posted by macropod

Try the following macro:

Sub UpdateRefs()
Dim StrFnd As String, StrRep As String
StrFnd = InputBox("Please input the string to FIND for the replacement.", "Find/Replace")
If StrFnd = "" Then Exit Sub
StrRep = InputBox("Please input the string with which to REPLACE the Find String.", "Find/Replace")
With Application
  .DisplayAlerts = False
  .ScreenUpdating = False
  .Calculation = xlManual
  .ActiveSheet.Cells.Replace What:=StrFnd, Replacement:=StrRep, LookAt:=xlPart, _
    SearchOrder:=xlByColumns, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
  .Calculation = xlAutomatic
  .DisplayAlerts = True
  .ScreenUpdating = True
End With
End Sub

[macropod]

Glad it worked for you.

I don't know of any particular Excel books (I've never bought one) - I've never done any formal programming study and I only dabble in Excel.