Page 1 of 2 12 LastLast
Results 1 to 10 of 12

Thread: Change cell value on multiple worksheets using a wildcard
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    Apr 2008
    Location
    Oregon
    Posts
    258
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Change cell value on multiple worksheets using a wildcard

    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

  2. #2
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    28,123
    Post Thanks / Like
    Mentioned
    470 Post(s)
    Tagged
    47 Thread(s)

    Default Re: Change cell value on multiple worksheets using a wildcard

    Which cell do you want to change?
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  3. #3
    MrExcel MVP
    Junior Admin
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    50,742
    Post Thanks / Like
    Mentioned
    57 Post(s)
    Tagged
    11 Thread(s)

    Default Re: Change cell value on multiple worksheets using a wildcard

    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 by Joe4; Sep 23rd, 2019 at 03:19 PM.
    TIPS FOR FINDING EXCEL SOLUTIONS
    1. Use the built-in Help that comes with Excel/Access
    2. Use the Search functionality on this board
    3. A lot of VBA code can be acquired by using the Macro Recorder.

    "Give a man a fish, feed him for a day. Teach a man to fish, feed him for life!"

  4. #4
    MrExcel MVP
    Junior Admin
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    50,742
    Post Thanks / Like
    Mentioned
    57 Post(s)
    Tagged
    11 Thread(s)

    Default Re: Change cell value on multiple worksheets using a wildcard

    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
        
        arr = Array("rateid*", "po*", "xyz*")
    
        '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 by Joe4; Sep 23rd, 2019 at 03:30 PM.
    TIPS FOR FINDING EXCEL SOLUTIONS
    1. Use the built-in Help that comes with Excel/Access
    2. Use the Search functionality on this board
    3. A lot of VBA code can be acquired by using the Macro Recorder.

    "Give a man a fish, feed him for a day. Teach a man to fish, feed him for life!"

  5. #5
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    28,123
    Post Thanks / Like
    Mentioned
    470 Post(s)
    Tagged
    47 Thread(s)

    Default Re: Change cell value on multiple worksheets using a wildcard

    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("A1").Value Like ary(i) Then
                ws.Range("A1").Value = "INPUT HERE"
                Exit For
             End If
          Next i
       Next ws
    Just change the value in red to suit
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  6. #6
    Board Regular
    Join Date
    Apr 2008
    Location
    Oregon
    Posts
    258
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Change cell value on multiple worksheets using a wildcard

    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

  7. #7
    MrExcel MVP
    Junior Admin
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    50,742
    Post Thanks / Like
    Mentioned
    57 Post(s)
    Tagged
    11 Thread(s)

    Default Re: Change cell value on multiple worksheets using a wildcard

    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.
    TIPS FOR FINDING EXCEL SOLUTIONS
    1. Use the built-in Help that comes with Excel/Access
    2. Use the Search functionality on this board
    3. A lot of VBA code can be acquired by using the Macro Recorder.

    "Give a man a fish, feed him for a day. Teach a man to fish, feed him for life!"

  8. #8
    Board Regular
    Join Date
    Apr 2008
    Location
    Oregon
    Posts
    258
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Change cell value on multiple worksheets using a wildcard

    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

  9. #9
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    28,123
    Post Thanks / Like
    Mentioned
    470 Post(s)
    Tagged
    47 Thread(s)

    Default Re: Change cell value on multiple worksheets using a wildcard

    @Joe4
    You missed the Cell from your first code
    Code:
        For Each ws In Worksheets
            ws.Cells.Replace What:="rateid*", Replacement:="INPUT HERE", LookAt:=xlPart, _
                SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
                ReplaceFormat:=False
            ws.Cells.Replace What:="po*", Replacement:="INPUT HERE", LookAt:=xlPart, _
                SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
                ReplaceFormat:=False
        Exit For
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  10. #10
    MrExcel MVP
    Junior Admin
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    50,742
    Post Thanks / Like
    Mentioned
    57 Post(s)
    Tagged
    11 Thread(s)

    Default Re: Change cell value on multiple worksheets using a wildcard

    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 by Joe4; Sep 23rd, 2019 at 04:23 PM.
    TIPS FOR FINDING EXCEL SOLUTIONS
    1. Use the built-in Help that comes with Excel/Access
    2. Use the Search functionality on this board
    3. A lot of VBA code can be acquired by using the Macro Recorder.

    "Give a man a fish, feed him for a day. Teach a man to fish, feed him for life!"

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •