YellowLabPro
07-29-2007, 03:16 AM
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:
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")
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
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:
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")
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