SOLVED

Search value in multiple Sheets - Fill adjacent cells when value is found

Contributor

Hi everyone.

 

In Sheet1!C11 I have a value X.
In Sheet 2 Table1, in Sheet 3 Table2 and in Sheet4 Table3.
Value X will appear only once in all of three Tables, in Column G.

 

I am in need of a macro that performs the following:
Search value X from cell Sheet1!C11 in all three sheets and when, for example, found in Sheet2!G25

1. Fill today's date in Sheet2!K25
2. Fill the word User1 in Sheet2!L25
3. Fill the word Cond1 in Sheet2!M25

 

Any help would be greatly appreciated.

9 Replies

Just bumping this up.

 

@Hans Vogelaar maybe you could be of assistance on this one?

 

Thanks.

@JoAvg 

Could you attach a sample workbook (without sensitive data), or if that is not possible, make it available through OneDrive, Google Drive, Dropbox or similar? Thanks in advance.

@Hans Vogelaar 

Made a dummy file keeping all the functions without the sensitive data.

Hope I shared it the right way..

https://1drv.ms/x/s!AvoleB1tsIJ8l2FgXbp1vjqpCcp3?e=s7jhvm

 

Will be waiting for your response.

Thank you!

 

 

@JoAvg 

Thanks, I have downloaded the workbook.

Can you now explain what you want using the sheet names and ranges of the dummy workbook?

@Hans Vogelaar 

Sorry for the late reply.

So, in Sheet SL&UR I got a search box in B3, which returns a code in C11. In the example the value is CP42735.

I want to search for the C11 value in Sheets SL_2019, SL_2021 and SL_2022 and in whatever row it is found I want to fill the adjacent rows in Columns L with Today() date, M with "Crew_1" and Q with "Drawn".

In the example the value is found in SL_2019!G13, so the end result would be:

SL_2019!L13=23/5/2022

SL_2019!M13=Crew_1

SL_2019!Q13=Drawn

 

Hope you can come with up with some of your magic.

@JoAvg 

See the attached version.

I moved the existing code from the SL&UR worksheet module to a standard module, and created an On Change event procedure for the search box in the worksheet module.

@Hans Vogelaar 

That is wicked!

Unfortunately, this step is going to be the last in my chain work so, this auto-populating event is of no help for me at all...

If you could take the time and restructure it so it works only when I call it, that would be great.

Thank you again for coping with me. I really appreciate it!

best response confirmed by JoAvg (Contributor)
Solution

@JoAvg 

Perhaps like this?

@Hans Vogelaar 

All hail Hans.

Yes! That is exactly it!

Thank you a million!