Change cell value on multiple worksheets using a wildcard

[03856me]

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?

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]

Which cell do you want to change?

 

[Joe4]

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.

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

 

[Joe4]

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.

    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.

 

[Fluff]

If it's only one cell on each sheet, you can do it like

   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]

Using your code I receive an error: Compile Error: Method or data member not found
The error highlights ws.Replace …..

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]

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]

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

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]

@Joe4
You missed the Cell from your first 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]

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).

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?