Search and Replace Combobox help!

[jjsauer]

Hello,
I created a macro that has a userform to select a workbook, pull data from it, and then go through the comments (its from a survey with an open-end question). The search and replace was designed to remove expletives. I wont post the expletive list, but I will share the rest. The red text below (cl.Value = sReplace) gives me an error: Run-time error '1004: Application -defined or object-defined error.

Any suggestions where I went wrong would be most helpful!


Private Sub CommandButton1_Click()
MyFile = Me.ComboBox1.Value
Workbooks(MyFile).Activate

Dim re As Object
Dim rng As Range, cl As Range
Dim sh As Worksheet
Dim wb As Workbook
Dim wb2 As Workbook
Dim sReplace As String
Dim aReplace(0 To 51, 0 To 51) As String
Dim i As Long

Workbooks(MyFile).Activate
Range("Q2:R" & Range("R65500").End(xlUp).Row).Copy
Windows("FD C-Sat Comment Creator for Portal.xlsm").Activate
Sheets("Comments").Activate
Range("A2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Workbooks(MyFile).Activate
Range("D2:D" & Range("D65500").End(xlUp).Row).Copy
Windows("FD C-Sat Comment Creator for Portal.xlsm").Activate
Sheets("Comments").Activate
Range("C2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Workbooks(MyFile).Activate
Range("G2:G" & Range("G65500").End(xlUp).Row).Copy
Windows("FD C-Sat Comment Creator for Portal.xlsm").Activate
Sheets("Comments").Activate
Range("E2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

Set wb = ActiveWorkbook
Set wb2 = Workbooks("FD C-Sat Comment Creator for Portal.xlsm")
Set re = CreateObject("vbscript.regexp")
re.Global = True
re.IgnoreCase = False
re.MultiLine = True

aReplace(0, 0) = "\bEXAMPLE\b"
aReplace(0, 1) = "[Expletive Deleted"



For Each sh In wb2.Worksheets
On Error Resume Next
Set rng = sh.UsedRange.SpecialCells(xlCellTypeConstants)
If Err.Number <> 0 Then
Err.Clear
Else
On Error GoTo 0
For Each cl In rng
sReplace = cl.Value
For i = 0 To UBound(aReplace, 1)
re.Pattern = aReplace(i, 0)
If re.test(sReplace) Then
sReplace = re.Replace(sReplace, aReplace(i, 1))
j = j + 1
End If
Next
cl.Value = sReplace
Next
End If
Next
Unload Me
MsgBox ("Number of expletives replaced:" & j)
Quarter = InputBox("What is the current Quarter in the format FYXX-QX", "Data File Quarter")
mySheet = (Quarter & "_Comments")
Application.Dialogs(xlDialogSaveAs).Show mySheet, xlCSV

 

[jjsauer]

bump