Find and Replace for several sheets

[ddnron]

Hi, Thanks in advance for your help.

This works fine for "Overview" worksheet.

I need this to run for two sheets, at the same time.

The target cells are different, on the sheets. "Overview" worksheet target cell is B2. For "Deposits" worksheet target cell is D7.

Need this to run for a worksheet called "deposits".... right after it runs for "Overview" worksheet.

Would appreciate! Thanks

Private Sub CommandButton3_Click() Dim userResponse As Integer userResponse = MsgBox("Use this only ONCE...at beginning of the year, to change the dates. Do you want to proceed?", vbYesNo) If userResponse = vbYes Then Sheets("Overview").Unprotect Sheets("Deposits").Unprotect Sheets("Overview").Select Range("b2").Select Cells.Replace What:=Application.InputBox("Year to search:", "Enter Year from Date in Left Corner", , , , , 2), Replacement:=Application.InputBox("Enter New Year", "Update to New Year", , , , , 2), LookAt:=xlPart, _ SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=False ElseIf userResponse = vbNo Then Else End If Sheets("Overview").Protect DrawingObjects:=False, Contents:=True, Scenarios:= _ False Sheets("Deposits").Protect DrawingObjects:=False, Contents:=True, Scenarios:= _ False End Sub

 

[jolivanes]

Add this following the "Dim UserResponse" line

Dim shArr(1 To 2, 1 To 2), i As Long
shArr(1, 1) = "Overview"
shArr(1, 2) = "B2"
shArr(2, 1) = "Deposits"
shArr(2, 2) = "D7"

Add this following the "If userResponse = vbYes Then"

For i = LBound(shArr) To UBound(shArr)
    With Sheets(shArr(i, 1))
        .Unprotect
            .Range(shArr(i, 2)).Replace What:= etc etc
        .Protect DrawingObjects:=False, etc etc
    End With
Next i
End If

Leave the ElseIf and Else lines out.


Please use code tags. Makes things so much easier to read/copy.

 

[ddnron]

Add this following the "Dim UserResponse" line

Dim shArr(1 To 2, 1 To 2), i As Long
shArr(1, 1) = "Overview"
shArr(1, 2) = "B2"
shArr(2, 1) = "Deposits"
shArr(2, 2) = "D7"

Add this following the "If userResponse = vbYes Then"

For i = LBound(shArr) To UBound(shArr)
    With Sheets(shArr(i, 1))
        .Unprotect
            .Range(shArr(i, 2)).Replace What:= etc etc
        .Protect DrawingObjects:=False, etc etc
    End With
Next i
End If

Leave the ElseIf and Else lines out.


Please use code tags. Makes things so much easier to read/copy.

Thanks Jolivanes - works! - and thanks for sharing your knowledge about code Tags!

 

[jolivanes]

Thanks for the update and good luck.