[VBA] Find & Replace In Workbook

Ark68

Well-known Member
Joined
Mar 23, 2004
Messages
3,976
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

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes

GWteB

Well-known Member
Joined
Sep 10, 2010
Messages
1,877
Office Version
  1. 2013
Platform
  1. Windows
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
 
Solution

Ark68

Well-known Member
Joined
Mar 23, 2004
Messages
3,976
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
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.
 

GWteB

Well-known Member
Joined
Sep 10, 2010
Messages
1,877
Office Version
  1. 2013
Platform
  1. Windows
You are welcome! Glad it's sorted and thanks for the feedback.
 

GWteB

Well-known Member
Joined
Sep 10, 2010
Messages
1,877
Office Version
  1. 2013
Platform
  1. Windows
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
 

Forum statistics

Threads
1,141,777
Messages
5,708,461
Members
421,571
Latest member
ChaosPup

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
Top