DeanP
06-21-2019, 08:32 AM
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.
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.