View Single Post
  #1   Report Post  
Posted to microsoft.public.excel.programming
CompleteNewb[_2_] CompleteNewb[_2_] is offline
external usenet poster
 
Posts: 30
Default 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?