I'm working on a project where I need to match up names in the same row but in 2 different columns. A have a macro written to work the way I want. I'm using names from Column D in an array and using the find function to match names in Column A. The successful find results is placed in Column B.
Presently, I'm adding the names from Column D into the array manually. I have tried adding these programmatically, but the macro fails.
Hoping someone will spot where I'm going wrong.
Sub MarkCellsInColumnA()
Dim FirstAddress As String
Dim lookfor As String
Dim MyArr As Variant
Dim Rng As Range
Dim lrA As Long, LrD As Long
Dim i As Long
lrA = Cells(Rows.Count, "A").End(xlUp).Row
LrD = Cells(Rows.Count, "D").End(xlUp).Row
With Application
.ScreenUpdating = False
.EnableEvents = False
End With
'The line directly below (commented out) is the one I wish to use
'but it does not work...
'MyArr = Range("D1:D" & LrD).Value
'The line directly below provides the results I reguire.
MyArr = Array("wally", "paul", "laura", "catherine", "pete", "ron", "bill", _
"geo", "george", "tom")
With Sheets("Sheet1").Range("A1:A" & lrA)
Range("B1:B" & lrA).ClearContents
For i = LBound(MyArr) To UBound(MyArr)
Set Rng = .Find(What:=MyArr(i), _
After:=.Cells(.Cells.Count), _
LookIn:=xlFormulas, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)
If Not Rng Is Nothing Then
FirstAddress = Rng.Address
Do
Rng.Offset(0, 1).Value = "x"
Set Rng = .FindNext(Rng)
Loop While Not Rng Is Nothing And Rng.Address <> FirstAddress
End If
Next i
End With
With Application
.ScreenUpdating = True
.EnableEvents = True
End With
End Sub
Thanks
Mister P
Bookmarks