View Single Post
  #2   Report Post  
Posted to microsoft.public.excel.programming
Claus Busch Claus Busch is offline
external usenet poster
 
Posts: 3,872
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