View Single Post
  #1   Report Post  
Posted to microsoft.public.excel.programming
James[_48_] James[_48_] is offline
external usenet poster
 
Posts: 6
Default Data Cleaning Macro Continues to generate Error

Hello - We created a Macro that cleans a series of Book Titles in Column A of Sheet 1.

The way it works is the Phrases in each Cell of Column A of Sheet 2 are reviewed sequentially. Then, a replace is done with whatever is in Column B of Sheet 2. So for example, if Sheet1!A1 has a value of "Dog Park", and Sheet2!A1 has a value of Dog, and Sheet2!B1 has a value of "Cat", then first step would be to replace the Word Dog in Sheet1!A1 to result in the value of "Cat Park".


Then, the loop continues for all values in SHeet2, Column A.


So that if there are 30,000 values in Column A, then each pass consists of 30,000 Finds and replaces of the first row's value from Sheet2 (Column A is the Find, and Column B is the Replace), then it moves to the second value in Sheet 2 and repeats, and so on.

This process cleans the contents of Sheet1 Column A (usually deleting the nondesirable values found in Sheet2, because most of Sheet2, Column B contains blanks (so the replace value is a blank).

The problem is that the file we have been using has suddenly been producing an error message that reads: Code Execution has been interrupted, and when we click on the Debug option, it shows the last "next" command is highlighted in yellow.

Could someone possibly help us solve this. Or, if you happen to have some code that will accomplish this using a fresh macro, that would work too.

I am happy to send you a copy of the file we are using, if it helps.

Thanks very much for any help.

Here is the code being used, FYI

Sub ReplaceIt()
Dim LRow1 As Long, LRow2 As Long
Dim i As Long
Dim varCheck As Variant

With Sheets("Sheet2")
LRow2 = .Cells(Rows.Count, 1).End(xlUp).Row
varCheck = .Range("A1:B" & LRow2)
End With

With Sheets("Sheet1")
LRow1 = .Cells(Rows.Count, 1).End(xlUp).Row
For i = LBound(varCheck) To UBound(varCheck)
.Range("A1:A" & LRow1).Replace What:=varCheck(i, 1), _
Replacement:=varCheck(i, 2), LookAt:=xlPart
Next
End With
End Sub