Using find within macro

Solved/Closed
Trowa - Sep 3, 2010 at 09:28 AM
aquarelle Posts 7140 Registration date Saturday April 7, 2007 Status Moderator Last seen March 25, 2024 - Sep 29, 2010 at 02:11 AM
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 responses

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
0
aquarelle Posts 7140 Registration date Saturday April 7, 2007 Status Moderator Last seen March 25, 2024 491
Sep 9, 2010 at 03:43 PM
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."
0
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
0
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 552
Sep 13, 2010 at 08:54 AM
Silly me, posted a protected workbook.
Here is the unprotected version: https://authentification.site/files/24231893/Kioskea_Verlofoverzicht.xls
0
aquarelle Posts 7140 Registration date Saturday April 7, 2007 Status Moderator Last seen March 25, 2024 491
Sep 21, 2010 at 02:44 PM
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."
0
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 552
Sep 27, 2010 at 09:40 AM
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
0
aquarelle Posts 7140 Registration date Saturday April 7, 2007 Status Moderator Last seen March 25, 2024 491
Sep 28, 2010 at 03:56 AM
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
0
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 552
Sep 28, 2010 at 09:16 AM
Wow Aquarelle, great solution, works like a charm.

Thank for helping me on this query.

See you later,
Trowa
0
aquarelle Posts 7140 Registration date Saturday April 7, 2007 Status Moderator Last seen March 25, 2024 491
Sep 29, 2010 at 02:11 AM
Hi,

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

See you later and have a nice day.
0