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,
False)
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?

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

Look at this:

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

Regards,
Per

On 18 Okt., 01:27, "CompleteNewb" wrote:
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,
False)
* * * * * * * * * * * * * * 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?

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

Why are you using Find and VlookUp?

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

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 " &
rngSearch.Address
Else
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