Using find within macro [Solved/Closed]

Report
-
Posts
7081
Registration date
Saturday April 7, 2007
Status
Anonymous
Last seen
September 25, 2020
-
Hello,

I have a list of first and last names of employees (Sheet1). Some of these employees have adjusted work schedules which are listed in a different sheet (Sheet2).
Now I would like to add a comment to the last names of sheet1 so you don't have to switch to sheet2 to find out when those employees work.

Sheet1 is made up like this:
Column A starting at row 8 contains the last names.
Column B starting at row 8 contains the first names.

Sheet2 is made up like this:
Column A starting at row 8 contains the last names.
Column B starting at row 8 contains the first names.
Column D starting at row 8 contains the hours for Monday.
Column E starting at row 8 contains the hours for Tuesday.
Column F starting at row 8 contains the hours for Wednesday.
Column G starting at row 8 contains the hours for Thursday.
Column H starting at row 8 contains the hours for Friday.

Since the list of employees isn't fixed I would like a macro which does the following:
Loop through the first and last names of sheet1 and compare it to the names of sheet2.
If a match is found add a comment to the cell which contains the last name of sheet1.
The comment should look like this:
"Ma:" & Cells(???rownumber???, "D").Value & Chr(10) & "Di:" & Cells(???rownumber???, "E").Value & Chr(10) & "Wo:" & Cells(???rownumber???, "F").Value & Chr(10) & "Do:" & Cells(???rownumber???, "G").Value & Chr(10) & "Vr:" & Cells(???rownumber???, "H").Value

Hopefully someone can help me especially with the finding/looping part of my query.

Best regards,
Trowa

3 replies

Please let me know if something is unclear with my query.

I have been trying everything I know, with no succes.

If you know how to solve halve the query, please let me know. So I can play around with it.

Best regards,
Trowa
Posts
7081
Registration date
Saturday April 7, 2007
Status
Anonymous
Last seen
September 25, 2020
498
Hi Trowa,

Could you post a workbook example? And I try to look at it soon as possible.

PS : When will you decide to become an en.kioskea member ? ;)
"Pour trouver une solution à ses problèmes, il faut s'en donner la peine."
Hi Aquarelle,

Nice to see again.
My workbook may look familiar to you, since you worked on it before.
Here it is: https://authentification.site/files/24190878/Verlofoverzicht_2010.xls

I have used sheet1 and sheet2 in my initial query to keep it simple.
Sheet1 are actually the sheets called Januari, Februari, Maart, April, Mei, Juni, Juli, Augustus, September, Oktober, November, December.
Sheet2 is the sheet called Afwijkende werkroosters.

Membership is coming up, I just didn't thought I would be using it so much.

Kind regards,
Trowa
Posts
2658
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
September 28, 2020
440
Silly me, posted a protected workbook.
Here is the unprotected version: https://authentification.site/files/24231893/Kioskea_Verlofoverzicht.xls
Posts
7081
Registration date
Saturday April 7, 2007
Status
Anonymous
Last seen
September 25, 2020
498
Hi Trowa,

I encountered some difficulties but I finished to find something. I did it just for januari sheet.

http://www.cijoint.fr/cjlink.php?file=cj201009/cijn3PXxRM.xls

Look at it and I hope that it will correspond to what you expected.

Maybe someone else could improved the macro to reduce its execution time.

Best regards :)

PS : I forgot to tell you that it is necessary to write name and first name exactly in the same way in every sheets. Otherwise, the macro will not see that they are the same. If you write a comma in a name, write also one on the other sheets.

"Pour trouver une solution à ses problèmes, il faut s'en donner la peine."
Posts
2658
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
September 28, 2020
440
Thanks Aquarelle, great code.

Now I'm thinking about when to activate the code. I don't want to bother the end user with to many buttons to click.
When running the code 12 times (one for each month) it takes a few seconds, which is ok if it only happens when nessecary. So I was thinking about a combination between "Sheet deactivate"and "Sheet change".
This because I don't want to run the codes just after checking the sheet "Afwijkende werkroosters" (Sheets deactivate).
Or when making a new entry I have to change 7 cells, then I don't want the code to be activated 7 times (Sheets change).

So do you know a way to run the codes after deactivating the sheet "Afwijkende werkroosters" only when a change has been made in the range of A8:H200?

Let me post my workbook again with the codes for the other months:
https://authentification.site/files/24435036/cijn3PXxRM.xls

Best regards,
Trowa
Posts
7081
Registration date
Saturday April 7, 2007
Status
Anonymous
Last seen
September 25, 2020
498
Hi Trowa,

I found a solution by putting a message box. Look at it and tell me what do you think about : http://www.cijoint.fr/cjlink.php?file=cj201009/cijnJCcorG.xls

Best regards
Posts
2658
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
September 28, 2020
440
Wow Aquarelle, great solution, works like a charm.

Thank for helping me on this query.

See you later,
Trowa
Posts
7081
Registration date
Saturday April 7, 2007
Status
Anonymous
Last seen
September 25, 2020
498
Hi,

Always a pleasure to help you when I have time and when I can :)

See you later and have a nice day.