Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old May 18th 17, 10:54 PM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Jun 2015
Posts: 4
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

  #2   Report Post  
Old May 18th 17, 11:05 PM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Apr 2011
Posts: 3,519
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Macro continues to run Rob Excel Programming 5 March 8th 10 01:22 AM
Can I create a macro to generate an email once the data is entered Susan Excel Discussion (Misc queries) 1 October 24th 08 03:02 AM
Fails then continues without error Joel Excel Programming 6 September 23rd 08 08:39 AM
Excel (or other) macro for cleaning date data [email protected] Excel Discussion (Misc queries) 3 July 25th 07 12:02 AM
generate a random number and use if function to generate new data Dogdoc1142 Excel Worksheet Functions 4 April 26th 06 03:44 AM


All times are GMT +1. The time now is 12:31 AM.

Powered by vBulletin® Copyright ©2000 - 2017, Jelsoft Enterprises Ltd.
Copyright 2004-2017 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017