Best way to run Find or Vlookup down specific column in range



I've set a range variable using 2 other range variables

BigRange = Range(StartRange,EndRange)

BigRange is several rows and columns (let's say StartRange is A4, EndRange
is AJ16).

I need to do a Find in another sheet for every value in the A (or 1st)
column of my BigRange. Currently I DO have a Find (Vlookup, really, but I
think a find would work, I'm starting to get a grasp on it) that works; I
just ignore Vlookup and/or Not FOund errors, so the code loops through every
cell in my range and only finds things that ARE in teh A column. It doesn't
take long or anything, I was just wondering if there's a way to only refer
to the 1st column in BigRange for doing the actual Find or Vlookup. I have:

For Each cell In BigRange
If Not IsError(Application.VLookup(cell.Value, 2ndRange, 1,
False)) Then
Cells(2ndRange.Find(cell.Value, LookIn:=xlValues,
LookAt:=xlPart).Row, 3rdRange.Column).Value =
Application.VLookup(cell.Value, shtName.Range(StartRange, EndRange), 5,
End If
Next cell

Instead of EVERY cell in BigRange, is there an easy way to just check the
cells in the qst COLUMN of BigRange?

Per Jessen

Look at this:

Set TestRng = Intersect(BigRange, Columns("A"))
For Each cell In TestRng



Why are you using Find and VlookUp?

You can do basically everying VLookup does using Find and a lot more

It's also a little easier to check to see if something is found.

Set rngFnd = rngSearchRange(What:=rngLookFor.Value...)

If rngFnd Is Nothing Then
' nothing found
Msgbox rngLookFor.Value & " is not found in range " &
Msgbox rngLookFor.Value & " found in row " & rngFnd.Row '
equivalent of MATCH worksheet function

Msgbox "Value 3 columns to right of " & rngLookForValue & " is " &
rngFnd.Offset(,3) ' VLOOKUP
End if

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question