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?

Look at this:

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

Regards,
Per

On 18 Okt., 01:27, "CompleteNewb" <newbiesupr...@yahoo.com> 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?

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