Replace

[Kiba]

I need a macro that can search several workbooks with alerts disabled find a
number say (5-510) in a several cells in several worksheets in each workbook
replace it with a new number say (5-511). Save and close. Also I need to
know which workbooks it changed values in. The name is also in cell c2 on a
Data Entry worksheet.

Thanks for the help.

I've tried several things but couldn't get them to work.

 

[Joel]

1) Where are the workbooks names? Or do you want to open all the workbooks
in a folder?
2) How do you want to finjd the numbers
a) Are the number in the a certain column, row, or Range of Cells on
each worksheet. Or the number in specific cells (name the cells).

 

[Kiba]

1) Its all the workbooks in a folder.
2) They are most likely several places in the workbook, and its going to be
different places in each workbook.

Also the number is probably in a cell with other text.

 

[Rick Rothstein]

Are the workbooks open? If so, then perhaps a macro like this will do what
you want...

Sub Find5Dash510()
Dim WB As Workbook
Dim WS As Worksheet
Dim ChangedWBs As String
For Each WB In Workbooks
For Each WS In WB.Worksheets
If Not WS.UsedRange.Find("5-510") Is Nothing Then
WS.UsedRange.Replace "5-510", "5-511"
ChangedWBs = ChangedWBs & WS.Name & vbCrLf
End If
Next
Next
If Len(ChangedWBs) > 0 Then
MsgBox "These workbooks were changed:" & vbCrLf & vbCrLf & ChangedWBs
Else
MsgBox "No workbooks were changed."
End If
End Sub

I wasn't sure what you wanted done when you said "the name is also in cell
c2 on a Data Entry worksheet", so I didn't address that in my code. If you
clarify what you wanted, and if the above does what you need, perhaps we can
change the code accordingly.

 

[Joel]

The question is if you search a Range of cells like A1100 will other
numbers get changed that shouldn't. Rick's code can be changed easily to
open all the workbooks in the folder. If the incorrect ce3lls may get
changged then you have to be more specific about the range of cells you are
changing.