Thanks for the help. As always, I learned something new.
I should have mentions that the value I'm searching for with VLOOKUP occurs several times in my list. These values represent the style of shoes, and the different rows are the sizes of that style. I must process all of them. After VLOOKUP returns the first occurrence of my search value, I use a While loop to process the other rows that contain that value.
I see that there are two ways to use VLOOKUP from within a VBA program. Bob Phillips (in an email that arrived in my inbox, not in the group's) sent this example
somevar = Application.Vlookup(lookup_val,lookup_range,3,False)
while I, taking a naive approach, enter into a cell exactly what I would write if I were not using VBA,
Range("D2").Formula = "=VLOOKUP(G2,StatTable,2,FALSE)"
and then I propagate that formula down the rest of the rows. In D3, the lookup value is stored in G3, etc.
Although all the examples in the links Randy sent use a variation of Bob's approach, I don't see how that is superior to my way. In Bob's approach, the statement must be in a For loop, which for thousands of rows could take considerable time, while with my approach, the formula is propagated in no time at all.
In any event, my original question was asking for the best way of finding the row where the lookup value was found. David suggested using Match. I've never totally understood Match, but I will give it another try.
Thanks again to all,
Michael Trombetta
[Non-text portions of this message have been removed]
| Reply via web post | Reply to sender | Reply to group | Start a New Topic | Messages in this topic (5) |
Be sure to check out TechTrax Ezine for many, free Excel VBA articles! Go here: http://www.mousetrax.com/techtrax to enter the ezine, then search the ARCHIVES for EXCEL VBA.
----------------------------------
Visit our ExcelVBA group home page for more info and support files:
http://groups.yahoo.com/group/ExcelVBA
----------------------------------
More free tutorials and resources available at:
http://www.mousetrax.com
----------------------------------
Tidak ada komentar:
Posting Komentar