Is there an easier way

[mp]

I have a "souce sheet" with two columns, one has a "name", the other a
number.
I will have all the names in a list

So I will loop through the list, get each name in turn, then find the
matching name row on the Source sheet and get the associated number - enter
where needed on target sheet
Repeat

I don't know an easier way than looping through the whole source sheet name
column
each time looking for a match to the "target name"

The names will probably be in the same order in my target list as in the
source sheet column
but it seems better programming not to rely on that being true necessarily.
If they were guaranteed to match I could use an index number and just
increment that for the
next row to check, but is there a more direct way?

Kind of like an sql statement something like(pseudocode) Get colB Where
colA.Value = TargetName

without having to repeatedly loop through the entire list each time?
Thanks
(hope that was clear)
Mark

 

[mp]

fwiw
here's the wasteful way, looping through the entire column on source sheet
each time
For lTicker = 0 To oDict.Count - 1
Ticker = oDict.KeyByIndex(lTicker)
For lSource = StartRow To LastRow
With SourceSht
If Trim(.Cells(lSource, "A").Value) = Trim(Ticker) Then
LastPrice = .Cells(lSource, "C").Value
Exit For
End If
End With
Next lSource
Next lTicker

 

[Jim Cone]

Or use a Vlookup formula in your list.
--
Jim Cone
Portland, Oregon USA .
http://www.mediafire.com/PrimitiveSoftware .
(XL Companion add-in: compares, matches, counts, lists, finds, deletes...)

 

[Don Guillett]

Or use a Vlookup formula in your list.
--
Jim Cone
Portland, Oregon USA .
http://www.mediafire.com/PrimitiveSoftware .
(XL Companion add-in: compares, matches, counts, lists, finds, deletes...)

Or use vba FIND instead of a loop

 

[mp]

Thanks Don and Jim
I'll check into those functions
Mark