I'm pretty new to VBA but over the last couple of months I've learned quite a bit by searching this forum but I can't seem to get over this hurdle. I have a workbook which contains 2 sheets. I'm trying to search for a range of values in sheet1 column B in sheet2 column B. If the value is found I am changing sheet2 column A to Delete. The problem I'm currently having is the macro finds only the first instance of the value and moves on to the next. I would like to find all instances of the value from sheet 1. Thanks in advance, any assistance provided will be greatly appreciated.
Below is my current code and attached is a copy of my test workbook.
Sub UpdateColumnA()
Dim cell As Range, rngFind As Range, Found As Range, Found1 As Range, counter As Long
Dim strFirstAddress As String
'List of values to search for from Sheet1 column B
With Sheets("Sheet1")
Set rngFind = .Range("B1", .Range("B" & Rows.Count).End(xlUp))
End With
For Each cell In rngFind
'Search in Sheet2 Column B
Set Found = Sheets("Sheet2").Range("B:B").Find(What:=cell.Value, _
LookIn:=xlValues, _
LookAt:=xlPart, _
MatchCase:=False)
If Not Found Is Nothing Then
strAddress = Found.Address
Set Found1 = Found
Do
'When a match is found, set Column A to Delete
Found.Offset(, -1).Value = "Delete" 'Value in Column A
counter = counter + 1
Set Found = Found.FindNext(Found)
Loop While Not Found Is Nothing And Found.Address <> strAddress
End If
Next cell
MsgBox "Replacements made: " & counter, , "Replacements Complete"
End Sub
Bookmarks