MULTIPLE FIND AND REPLACE FROM USERFORM INPUT

[DeanP]

I'm having difficulty finding the best solution for what I thought would be a fairly simple find and replace using VBA. I have multiple worksheets in my workbook in which I need to find and replace data from userform input. I can find bits and pieces of code but can't seem to bring it all together.

First, my userform.
It has 6 textboxes. TB 2-6 all autofill based on the input in TB 1.
TB2 = TB1 +1, TB3 = TB1 - 1, TB4 = "30 June" & TB2, TB5 = "30 June" & TB3 and TB6 = TB1 - 1
In my current workbook the text box values will be: TB2 = 2019, TB3 = 2018, TB4 = 30 June 2019, TB5 = 30 June 2018 and TB6 = 2017

I think I'm ok with how all of those textboxes are being autofilled. However, I also have a message box (launched from a command button in my userform) with an OK/Cancel choice. If, cancel is selected the procedure ends. If OK is selected, then the rest of the code continues.

This what I have for the textboxes:

Private Sub TextBox1_Change()


    TextBox2.Value = TextBox1.Value + 1
    TextBox4.Value = "30 June" & " " & TextBox2.Value
    TextBox6.Value = TextBox1.Value - 1
    
End Sub


Private Sub TextBox2_Change()


    TextBox3.Value = TextBox1.Value
    TextBox5.Value = "30 June" & " " & TextBox3.Value
    
End Sub

For the commandbutton I have the following, but I'm not sure it's correct for allowing the rest of the code to continue

Private Sub CommandButton1_Click()
Dim myAnswer As Integer
    
    MsgBox "Roll forward cannot be reversed! Please ensure you have a backup.", vbCritical + vbOKCancel, "Warning!"


    If myAnswer = vbCancel Then
        Exit Sub
            Else
                Call FindAndReplace
                    End If
End Sub

Second, find and replace.
I need to do 2 types of find and replace: (a) in each spreadsheet in the workbook I need to find cells = TB3 and replace with TB2 value, and find cells = TB6 and replace with TB3 value; (b) in each spreadsheet I need to find value of TB5 in string and replace with TB4 value.

Here I have a few difficulties. The code I have so far to replace single values (but incomplete) is:

Sub FindAndReplace()
Dim x As Variant, y As Variant, z As Variant
Dim myRange As Range
Dim TB2 As Long, TB3 As Long, TB6 As Long
Dim TB4 As String, TB5 As String
Dim ws As Worksheet
Dim LRow As Long
Dim i As Long

LRow = ws.UsedRange.Rows(ws.UsedRange.Rows.Count).Row

Set myRange = LRow

TB2 = UserForm3.TextBox2.Value
TB3 = UserForm3.TextBox3.Value
TB4 = UserForm3.TextBox4.Value
TB5 = UserForm3.TextBox5.Value
TB6 = UserForm3.TextBox6.Value

x = TB2, Y = TB3, z = TB6

For each ws In ActiveWorkbook.Worksheets
       With ws.myRange
         For each
         ??

And to replace only some of the characters in a string (i.e. 30 June .....)

i = 12
str1 = TB5
str1 = Right(str1, 1, i + 1) & Replace(str1 TB5, TB4, start:=1)

The values that need to be replaced can be in different cells in different worksheets. The text string containing 30 June xxxx could also not be in the same cells or in the same position within text in each of the sheets (i.e. could have At 30 June in one sheet and For the 52 weeks ending 30 June... in another)

Any advice would be appreciated.

[yujin]

Can you upload a sample file?

[Paul_Hossler]

Couple of mistakes in your code

This is just based on my recreating some test data that might be close to yours since there was no sample workbook with userform and worksheets

Option Explicit

Sub FindAndReplace()

    Dim TB2 As Long, TB3 As Long, TB6 As Long
    Dim TB4 As String, TB5 As String
    Dim ws As Worksheet
    Dim rNumbers As Range, rCell As Range
    Dim s As String


    'this is a long, i.e. row number
'    LRow = ws.UsedRange.Rows(ws.UsedRange.Rows.Count).Row
    'you can't set a Range to it
'    Set myRange = LRow


'   changed to test
    TB2 = 22    '   UserForm3.TextBox2.Value
    TB3 = 33    '   UserForm3.TextBox3.Value
    TB4 = 44    '   UserForm3.TextBox4.Value
    TB5 = 55    '   UserForm3.TextBox5.Value
    TB6 = 66    '   UserForm3.TextBox6.Value

    For Each ws In ActiveWorkbook.Worksheets
           
        Set rNumbers = Nothing
        On Error Resume Next
        Set rNumbers = ws.UsedRange.SpecialCells(xlCellTypeConstants, xlNumbers)
        On Error GoTo 0
       
       If Not rNumbers Is Nothing Then
            rNumbers.Replace What:=TB3, Replacement:=TB2, LookAt:=xlWhole
            rNumbers.Replace What:=TB6, Replacement:=TB3, LookAt:=xlWhole
            rNumbers.Replace What:=TB5, Replacement:=TB4, LookAt:=xlPart
        End If
    Next
End Sub

[DeanP]

Here is my userform:

Userform.jpg
Starting from the first box the text box order is TB1, TB6, TB2, TB3, TB4, TB5.

All of the userform code:

Private Sub TextBox1_Change()

    TextBox2.Value = TextBox1.Value + 1
    TextBox4.Value = "30 June" & " " & TextBox2.Value
    TextBox6.Value = TextBox1.Value - 1
    
End Sub


Private Sub TextBox2_Change()


    TextBox3.Value = TextBox1.Value
    TextBox5.Value = "30 June" & " " & TextBox3.Value
    
End Sub


Private Sub CommandButton1_Click()
Dim myAnswer As Integer
    
    MsgBox "Roll forward cannot be reversed! Please ensure you have a backup.", vbCritical + vbOKCancel, "Warning!"


    If myAnswer = vbCancel Then
        Exit Sub
            Else
                Call FindAndReplace
                    End If
End Sub

My main workbook has several sheets with the values I want to find and replace, but the values could be in different cells on different sheets, or the text string could differ slightly from sheet to sheet as in the screenshot below:

Sheets.PNG

@ Paul - I tried to run your code, but I'm getting a type mismatch error here

TB2 = UserForm3.TextBox2.Value