Change cell value on multiple worksheets using a wildcard

03856me

Active Member
Joined
Apr 4, 2008
Messages
297
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
 

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.
Which cell do you want to change?
 
Upvote 0
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:
Upvote 0
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:
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
@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
 
Upvote 0
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:
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,208
Members
448,554
Latest member
Gleisner2

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