[VBA] Find & Replace In Workbook

Ark68

Well-known Member
Joined
Mar 23, 2004
Messages
4,562
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
I have a range of data in column A (A2:A15). They all contain numeric 8 digit values, the first 5 numbers representing a serial date and the remaining three a unique incremental number (001...015). A value would look like 44362001 ,,, 44362015. The values may not fill the row sequentially.

I am looking for a vba solution that will replace these values with a new preceeding 5 digits (serial date), and the sequence portion as "xxx". I would need to do this for all worksheets in the workbook. These values do not need to be numbers, they can be text. They are not used in any calculations and are just for referencing.

I ran into two issues as I used the macro recorder to get the code for this process. (Find 44362, replace with 44364 in the workbook). The code didn't appear to reference the need to find/replace in the workbook. Maybe it does, but I wasn't sure how to adapt it to my specific code.
This is what it gave me:
VBA Code:
Sub Macro1()
' Macro1 Macro
    Cells.Replace What:="44362", Replacement:="99999", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False, FormulaVersion:=xlReplaceFormula2
End Sub

I adapted it to my specific code ...
Rich (BB code):
Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address(False, False) = "M1" Then
        If IsDate(Target) = False Then
            MsgBox "Please enter a valid date."
            mbevents = False
            Worksheets("Master").Range("M1") = dt_prvdate
            Exit Sub
        End If
        dt_crtdate = Worksheets("Master").Range("M1").Value
        sr_crtdate = CLng(dt_crtdate)
        With ThisWorkbook
            .Cells.Replace What:=sr_prvdate, Replacement:=sr_crtdate, LookAt:=xlPart, _
            SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
            ReplaceFormat:=False, FormulaVersion:=xlReplaceFormula2
        End With
    End If
End Sub

I was getting an error with the '.cells' reference as highlighted in red.

The secode issue, is I have no idea how to replace the different trailing 3 digits to "xxx". If I had a working find and replace code, it would only make changes to the first 5 digits.

Thoughts.
 

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand
My thoughts:
1. Cells is a Range property of the Worksheet object (and not the Workbook object), causing you an error;
2. Since you need the replacement on all the worksheets of your workbook, you need to iterate through the workbook.
3. The Cells property represents all the cells on the worksheet. Since your data to be replaced resides in column A it might be prudent to narrow down the range before any replacement takes place;
4. The last parameter of the Replace method (FormulaVersion:=xlReplaceFormula2) is a new and undocumented one and might cause an error if there's nothing to replace. Just omit it and you're always fine.
5. Using wildcards like "???" fills your need to replace the trailing three digits.

Regarding the above, your code snippet below ...
Code:
    With ThisWorkbook
        .Cells.Replace What:=sr_prvdate, Replacement:=sr_crtdate, LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False, FormulaVersion:=xlReplaceFormula2
    End With


... could look like this:
VBA Code:
    Dim sht As Worksheet
    For Each sht In ThisWorkbook
        sht.Columns("A").Replace What:=sr_prvdate & "???", Replacement:=sr_crtdate & "xxx", _
                                 LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, _
                                 SearchFormat:=False, ReplaceFormat:=False
    Next sht
 
Upvote 0
Solution
Thank you GWteB for sharing your skill. You're solution along with an explanation not only solved my issue, but was a great educational opportunity.
 
Upvote 0
You are welcome! Glad it's sorted and thanks for the feedback.
 
Upvote 0
You're solution along with an explanation not only solved my issue, but was a great educational opportunity.
If I had paid more attention to the code than the explanation, I should have discovered for myself that an omission crept into the code:
For Each sht In ThisWorkbook should be:
VBA Code:
For Each sht In ThisWorkbook.Worksheets
 
Upvote 0

Forum statistics

Threads
1,213,487
Messages
6,113,943
Members
448,534
Latest member
benefuexx

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top