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
 
You missed the Cell from your first code:wink:
Thanks for the catch on the first code.
The second resvised one I posted right after that should be good though. I tested that one and it worked for me.
 
Upvote 0

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.
Yup, your second code looks fine to me.

@3856me
Judging by the message in the msgbox, it looks as though you are looking to replace the text in a fixed range of cells, regardless of the current cell content.
Is that right?
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,251
Members
448,556
Latest member
peterhess2002

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