May 20 2021 07:38 AM
Hello everyone,
I would like to know how to code a macro for searching partial data match in other sheet (or other workbook).
For example : <If current sheet name (or string of cell A1) is (partially) found in range "A1:C20" of sheet 1 (database sheet), show value in cell C1 of current sheet>.
Any help would be greatly appreciated, thanks in advance :)
May 20 2021 08:10 AM
Your description is sufficiently vague that I feel the need to ask that you post an example of the worksheet(s) you have, or a mock-up of them if that's necessary to avoid posting confidential information.
It's entirely possible that you don't need a macro; Excel has some marvelously powerful functions that can retrieve info from other sheets or other workbooks. But either way--macro OR function--it would help to see exactly the situation. Without that, you can't expect more than generalizations in the way of help.
May 21 2021 12:36 AM
Thank you for your reply, please find attached the mock-up file.
I would probably need a macro because I have more than 200 sheets to run the same action.
For example I need to fill the cell L8 of every sheet with the address data in range D2:D6 of addressDATA sheet according to the name matching (partially) in the range B2:B6.
I would just use a VLOOKUP function if i had only few sheets, but it's not the case.
Thank you in advance for your help :D
May 21 2021 09:49 AM
Solution
May ask, now that I've seen the sample, why there are more than 200 sheets in each of which cell L8 needs to be filled with that address data from the database sheet?
I don't mean to be impertinent in asking. There may be entirely legitimate reasons, and if so perhaps a macro is indeed the solution. I'm not a macro person; I am more of a design person who strives to use Excel with its built-in functions, often finding that there are far more elegant solutions than (what I will refer to as) the "brute force" of a macro.
So in this case, where all of the subsequent sheets are exactly the same format, it makes me wonder whether they could all be consolidated into:
As I said, there may be a reason why that can't be done, but I view part of my (volunteer) job here in the techcommunity to raise such questions about design rather than simply give answers, where I think such questions are warranted.
May 21 2021 09:49 AM
Solution
May ask, now that I've seen the sample, why there are more than 200 sheets in each of which cell L8 needs to be filled with that address data from the database sheet?
I don't mean to be impertinent in asking. There may be entirely legitimate reasons, and if so perhaps a macro is indeed the solution. I'm not a macro person; I am more of a design person who strives to use Excel with its built-in functions, often finding that there are far more elegant solutions than (what I will refer to as) the "brute force" of a macro.
So in this case, where all of the subsequent sheets are exactly the same format, it makes me wonder whether they could all be consolidated into:
As I said, there may be a reason why that can't be done, but I view part of my (volunteer) job here in the techcommunity to raise such questions about design rather than simply give answers, where I think such questions are warranted.