Unclear by use of CurrentRegion.Address in Macro

[capson]

I have the Macro which works for this set up:

Sheet("FindReplace")

Please Login or Register  to view this content.

Sheet("Age")

Please Login or Register  to view this content.

Please Login or Register  to view this content.

But I Can NOT get it to works for this set up (Sheets("Age") remains the same) :

Sheet("FindReplace2")

Please Login or Register  to view this content.

Please Login or Register  to view this content.

Thanks

[AB33]

Look up works only to the left of the column. You need to rearrange your columns or use I and M.

Please Login or Register  to view this content.

[AB33]

Hi Capson,
See the attached which shows how Vlookup works. I personally avoid it and use Index and match.

[capson]

Thank you for both!

I have used Match in the past and I had a lot of trouble with it it was on a large data set (about 300,000 rows) it seemed slow and prone to throwing errors.

Maybe it was me and how I used it, but it left me with a avoidance to Match.

Do you find match to be touchy and slow on large data sets? in particular when Matching across sheets, if you don't I will give it a try again

I was supersized how fast VLookUp was, which was why I was using it

[StuCram]

My experience:

VLOOKUP() is great for SORTED sets of data; that contributes to its speed.
MATCH() will search unsorted data sets and therefor is not as fast to do its job.

Use MATCH() when its needed just once or a few times.
If searching is needed often, then sort the data set first and search with VLOOKUP()

[AB33]

Yes, I do not like the match function on it own and hardly used it. I prefer a find function which is robust and fast. Index and match (I and M) is a different concept. I do not know if IM is slower than Vlook, but Vlook has some draw backs. It can only return from the left part of the column as I have shown you on the attached. Yes, there are a couple of round ways of this issue, buy why bother. Imagine you insert a new column and as a result the column numbers change, the Vlook will not work, worse it gives a false result as the look column is hard coded. IM does not suffer from this as it use the column as a reference.

[capson]

Thank you again