Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Data Cleaning Macro Continues to generate Error
Hi James,
Am Thu, 18 May 2017 14:54:01 -0700 (PDT) schrieb James: 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. there is missing a period in front of Rows.Count Try: 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 Regards Claus B. -- Windows10 Office 2016 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro continues to run | Excel Programming | |||
Can I create a macro to generate an email once the data is entered | Excel Discussion (Misc queries) | |||
Fails then continues without error | Excel Programming | |||
Excel (or other) macro for cleaning date data | Excel Discussion (Misc queries) | |||
generate a random number and use if function to generate new data | Excel Worksheet Functions |