Which method: Vlookup, InStr, Other?

First getting something to work is the challenge, then choosing which method to do the job is the next challenge-- As I learn more about all the different methods to accomplish a job I find myself asking, what is the best method?
I currently have a task of finding and replacing a list of values. But which is the best method w/ regards to speed/performance and the "correct" method, (a few of the members here point out to me that things may work, but there may be problems w/ my "method of choice" or other things going on in the background I may not be aware of yet.... Malik and Xld :hi: )

The list will be for the most part static, so needing to add values to a list to keep it updated is not a big concern- this is where I tend to think an "InStr" approach may be the way to go, rather than a VLookup which I think is going to be slower due to its design to go out to the sheet and look at the values and compare and replace, but would like to make sure I am not missing something here w/ my evaluation of the functions. I have tended to use the InStr alot lately and maybe just my lack of knowledge as to a better approach.

Valid Values in my List: Terms in left column are Find values, Terms in the right column are the Replace.
This would be my approach:
[vba]
For Each c In Range("M4:M" & LRowF)
If InStr(c.Value, "L") > 0 Then c.Value = Replace(c.Value, "L", "Lg")
For Each c In Range("M4:M" & LRowF)
If InStr(c.Value, "LARGE") > 0 Then c.Value = Replace(c.Value, "LARGE", "Lg")
[/vba]

L...............Lg
LARGE......Lg

LG.............Lg

LRG...........Lg

L/Xl............Lg/Xl
M...............Md
MD.............Md
MED.......... Md
S................Sm
SM.............Sm
SML...........Sm
S/M............Sm/Md
XL.............. Xl
Xlarge..........Xl
XLG.............Xl
etc...
(Sorry formatting is impossible)

Thanks for having a look

Doug

Instr and VLOOKUP are two different beasts, I cannot see that you would ever need to consider which of these tow to use. Instr looks for a particular string within another string and returns its poistion if found, whereas VLOOKUP looks for a value in a range/array of values and returns a value offset fro the found value.

Instr would more realistically be compared against the Worksheet Find and/or Search functions. VLookup (or Match) would better be compared against VBA's Find.

In gen eral terms (and this is general, it isn't always clear cut), I prefer not to use Excel functions in VBA, but to use the native functions. It is generally more portable, and faster.

But why do you have the loop within a loop over the same range. Sounds like a situation for Select Case.

And do you actually need Instr at all if comparing the whole string?

What is the VBA comparative native function for VLookup, Select Case or is there not a specific one and Select Case would be the closest one to it?

What do you mean about a loop w/in a loop?

I do see your point about replacing a partial vs. a whole word match, how can I rewrite this w/out the InStr? I used InStr in other bits and would like to go back and fix.

Couldn't you actually use Find/Replace?

This is your loop within a loop:
[VBA]For Each c In Range("M4:M" & LRowF)
If InStr(c.Value, "L") > 0 Then c.Value = Replace(c.Value, "L", "Lg")
For Each c In Range("M4:M" & LRowF)[/VBA]
It means that for each cell in the range , you are looping through every cell in the range, so most cells get checked multiple times.
Also, if you are using Replace, you do not need InStr at all, since Replace will replace the text if it is there, and won't if it isn't.
For what you are doing, I think you would be better off having a lookup table in a defined range then using Application.VLookup to return the "translation" value for each cell; or you could load the table into an array, and iterate through the array using the Range.Replace method to simply replace all occurrences within your range in one go, rather than looping through every cell.
Regards,
Rory

Rory,

Doug and I had an offline conversation yesterday and discussed this. In actuality it was a transcription error, the code wasn't really like that. But Doug is changing the way it processes anyway.

More helpfully (to Doug), he now understands a previous coment of yours where you suggested that he was looping the same cells twice.

Glad to hear it! Nice to know someone can explain what I'm talking about... :)
Thanks for letting me know.
Rory