0

I am new to VBA programming (about 3 months) and have heavily relied on the internet for all my answers, however, I have been unable to find an answer that solves what seems like a simple problem.

I have two ranges and I need to cycle through all the names in one range (Column "R") and search the other range (zerange) for each name. If any of the names from Column "R" are not in zerange, those names need to be added to the first available row in zerange.

The best solution I could find was using a 'Do Until' loop to cycle through Column R and use the 'Intersect' function to check if the ActiveCell value (from Column "R") appears in zerange.

When running the procedure, 'If Not' results in finding no matches, while using 'If' results in the MsgBox appearing on each Column "R" value.

Below is the code I am using. Thankyou in advance for any advice.

Sub importlid()

Dim zerange As Range

Set zerange = Sheets("Details").Range("B1", Range("B" & Rows.Count).End(xlUp))

Sheets("Live Sheet").Range("P:P").AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Sheets("Details").Range("R:R"), Unique:=True

Sheets("Details").Range("R1").Select

    Do Until ActiveCell = vbNullString

        If Not Application.Intersect(ActiveCell, zerange) Is Nothing Then

            MsgBox "We got one!"

        End If

    ActiveCell.Offset(1, 0).Select

    Loop

End Sub
Community
  • 1
  • 1
Paul Murph
  • 1
  • 2
  • In the code above I have been using the MsgBox as a temporary replacement for the procedure that copies unmatched names into the next available row of column B. – Paul Murph Jun 15 '16 at 09:38
  • 1
    intersect checks if ranges overlap (what isnt the case). You have to compare the values. Try using `Find`. – Jochen Jun 15 '16 at 09:42

0 Answers0