Change cell value on multiple worksheets using a wildcard

03856me

Active Member
My workbook contains many sheets and before closing the workbook I would like to replace the same cell on all worksheets with the same text. For example, certain cells replace all that start with "rateid_???". I have tried the following code but receive an error on the IF ws.Range…… row. Could someone help me with the syntax?

Code:
Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Result = MsgBox("Your file will be reset and INPUT HERE will replace all header fields !!!", vbOKCancel + vbCritical)
    If Result = vbOK Then
        
    Dim ws As Worksheet
    For Each ws In Worksheets
        If ws.Range.Cells.Value Like "rateid*" Then cell.Value = "INPUT HERE"
        If ws.Range.Cells.Value Like "po*" Then cell.Value = "INPUT HERE"
'=== FYI ===I will need to add about 8 more rows to replace with IMPUT HERE code
Next ws
        
        
        Else: End If
    
End Sub
 

Joe4

MrExcel MVP, Junior Admin
If ws.Range.Cells.Value Like "rateid*" Then cell.Value = "INPUT HERE"
You cannot change a whole range of cells at once for a single value like that.
You will need to use something like find, i.e.
Code:
For Each ws In Worksheets
    ws.Replace What:="rateid*", Replacement:="INPUT HERE", LookAt:=xlPart, _ 
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    ws.Replace What:="po*", Replacement:="INPUT HERE", LookAt:=xlPart, _ 
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
Next ws
 
Last edited:

Joe4

MrExcel MVP, Junior Admin
Just an improvement upon my previous reply. If you have 10 values like this you want to replace, you can store them in an array and loop through them (so you do not need a separate code block for each one), i.e.
Code:
    Dim arr As Variant
    Dim i As Long
    Dim ws As Worksheet
    
    [COLOR=#0000ff]arr = Array("rateid*", "po*", "xyz*")[/COLOR]

    'Loop through all sheets
    For Each ws In Worksheets
        'Loop through all values in array
        For i = LBound(arr) To UBound(arr)
            On Error Resume Next
            ws.Cells.Replace What:=arr(i), Replacement:="INPUT HERE", LookAt:=xlPart, _
                SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
                ReplaceFormat:=False
            On Error GoTo 0
        Next i
    Next ws
You can add all the values you are replacing to the array in blue above.
 
Last edited:

Fluff

MrExcel MVP, Moderator
If it's only one cell on each sheet, you can do it like
Code:
   Dim ws As Worksheet
   Dim ary As Variant
   Dim i As Long
   arr = Array("rateid*", "po*", "xyz*")
   For Each ws In Worksheets
      For i = 0 To UBound(ary)
         If ws.Range("[COLOR=#ff0000]A1[/COLOR]").Value Like ary(i) Then
            ws.Range("[COLOR=#ff0000]A1[/COLOR]").Value = "INPUT HERE"
            Exit For
         End If
      Next i
   Next ws
Just change the value in red to suit
 

03856me

Active Member
Using your code I receive an error: Compile Error: Method or data member not found
The error highlights ws.Replace …..
Code:
Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Result = MsgBox("Your file will be reset and INPUT HERE will replace all header fields !!!", vbOKCancel + vbCritical)
    If Result = vbOK Then
        
    Dim ws As Worksheet
    Dim arr As Variant
    arr = Array("rateid*", "po*", "contract", "landowner*")
    For Each ws In Worksheets
        ws.Replace What:="rateid*", Replacement:="INPUT HERE", LookAt:=xlPart, _
            SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
            ReplaceFormat:=False
        ws.Replace What:="po*", Replacement:="INPUT HERE", LookAt:=xlPart, _
            SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
            ReplaceFormat:=False
    Exit For
    Next ws
End If
End Sub
 

Joe4

MrExcel MVP, Junior Admin
Using your code I receive an error: Compile Error: Method or data member not found
It probably means that it did find one of the values on one of the sheets.
Use the updated version I posted in my last post, and it should skip those type of errors.
 

03856me

Active Member
This code did not work - no errors and exited without changing any values

Code:
Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Result = MsgBox("Your file will be reset and INPUT HERE will replace all header fields !!!", vbOKCancel + vbCritical)
    If Result = vbOK Then
        
    Dim arr As Variant
    Dim i As Long
    Dim ws As Worksheet
    
    arr = Array("rateid*", "po*", "contract*", "landowner*")
    'Loop through all sheets
    For Each ws In Worksheets
        'Loop through all values in array
        For i = LBound(arr) To UBound(arr)
            On Error Resume Next
            ws.Cells.Replace What:=arr(i), Replacement:="INPUT HERE", LookAt:=xlPart, _
                SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
                ReplaceFormat:=False
            On Error GoTo 0
        Next i
    Next ws
End If
End Sub
 

Fluff

MrExcel MVP, Moderator
@Joe4
You missed the Cell from your first code;)
Code:
    For Each ws In Worksheets
        ws[COLOR=#ff0000].Cells[/COLOR].Replace What:="rateid*", Replacement:="INPUT HERE", LookAt:=xlPart, _
            SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
            ReplaceFormat:=False
        ws[COLOR=#ff0000].Cells[/COLOR].Replace What:="po*", Replacement:="INPUT HERE", LookAt:=xlPart, _
            SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
            ReplaceFormat:=False
    Exit For
 

Joe4

MrExcel MVP, Junior Admin
This code did not work - no errors and exited without changing any values
It worked for me. I made one minor change to declar the RESULT variable (because I have Option Explicit turned on).
Code:
Private Sub Workbook_BeforeClose(Cancel As Boolean)

    Dim Result
    Dim arr As Variant
    Dim i As Long
    Dim ws As Worksheet
    
    Result = MsgBox("Your file will be reset and INPUT HERE will replace all header fields !!!", vbOKCancel + vbCritical)
    If Result = vbOK Then
 
        arr = Array("rateid*", "po*", "contract*", "landowner*")
        'Loop through all sheets
        For Each ws In Worksheets
            'Loop through all values in array
            For i = LBound(arr) To UBound(arr)
                On Error Resume Next
                ws.Cells.Replace What:=arr(i), Replacement:="INPUT HERE", LookAt:=xlPart, _
                    SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
                    ReplaceFormat:=False
                On Error GoTo 0
            Next i
        Next ws
    End If

End Sub
When I closed my workbook, I got the MsgBox, which I said OK to, then it made the changes, and asked my if I wanted to save the file.
So it all appears to work as it should.

What is happening when you try?
Are you getting the MsgBox?
Save prompt?
 
Last edited:

Some videos you may like

This Week's Hot Topics

  • Importing multiple excel files into one spreadsheet
    Hi, I'm trying to import multiple excel files (with the same format into a single spreadsheet) so that each day's file is listed underneath the...
  • find many based on a certain criteria
    good evening, I hope someone can help me? I have a workbook sheet 2 contains lots of data.... I would like to be able to find anything on sheet...
  • How to copy multiple rows using If
    Hi all, I'm very new to VBA and have written this simple code to copy certain cells if a certain cell within that row contains any data. I need...
  • VBA If statement
    Dear All, I have two dates, where I'd like a message box to pop, if the dates are between this criteria. [CODE] sDate1 = #10/1/2019#...
  • Text Format
    I have a sheet for user to keyin the data. The format of the data can be 451 / 1903, 0012 / 9908 or 00287 / 0099. The number after the "/" is...
  • Macro to copy values across rows and transposing them and add the user id
    [FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][FONT=Calibri][SIZE=3][COLOR=#000000]Hi,[/COLOR][/SIZE][/FONT] [FONT=Times New...
Top