Change cell value on multiple worksheets using a wildcard

03856me

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

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
30,247
Office Version
365
Platform
Windows
Which cell do you want to change?
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
51,135
Office Version
365
Platform
Windows
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
Joined
Aug 1, 2002
Messages
51,135
Office Version
365
Platform
Windows
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
Joined
Jun 12, 2014
Messages
30,247
Office Version
365
Platform
Windows
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
Joined
Apr 4, 2008
Messages
259
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
Joined
Aug 1, 2002
Messages
51,135
Office Version
365
Platform
Windows
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
Joined
Apr 4, 2008
Messages
259
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
Joined
Jun 12, 2014
Messages
30,247
Office Version
365
Platform
Windows
@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
Joined
Aug 1, 2002
Messages
51,135
Office Version
365
Platform
Windows
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:

Forum statistics

Threads
1,077,827
Messages
5,336,616
Members
399,093
Latest member
chado4250

Some videos you may like

This Week's Hot Topics

Top