Help creating a worload list for undividual people based on data in different sheets.

Thought that I would keep my thread going with the progress I'm making. I have had success in creating a small sub to populate the site_schedule list. Now going for the kill and seeing if I can get the single script to populate the user1 user2 and user3 sheets as it populates the schedule table.

If I can I will get the sub to reset the users sheet and populate it with their tasks as it allocates.

Current code as follows.

Sub update_user()
Dim schednum As Integer 'only a whole number
Dim sitenum As Integer 'only a whole number
Dim r1 As Integer 'only a whole number
Dim r2 As Integer 'only a whole number
Dim found As Boolean 'only true or false

schednum = Application.WorksheetFunction.CountA(Worksheets("site_schedule").Columns(2)) - 1 'check the number of scheduled items
sitenum = Application.WorksheetFunction.CountA(Worksheets("site_user").Range("b:b")) - 1 'check the number of sites

' For each entry in the second worksheet, see if it's
' in the first.
For r2 = 1 To schednum
found = False
Worksheets("site_schedule").Cells(1, 4) = found 'just here to check the numbers as we go
Worksheets("site_schedule").Cells(2, 6) = r2 'just here to check the numbers as we go
Worksheets("site_schedule").Cells(2, 5) = sitenum 'just here to check the numbers as we go
' See if the r1-th entry on sheet 2 is in the sheet
For r1 = 1 To sitenum ' 1 list.
Worksheets("site_schedule").Cells(1, 5) = r1 'just here to check the numbers as we go
Worksheets("site_schedule").Cells(1, 6) = schednum 'just here to check the numbers as we go
If Worksheets("site_user").Cells(r1 + 3, 2) = Worksheets("site_schedule").Cells(r2 + 3, 2) Then
' If a match is found
found = True
Worksheets("site_schedule").Cells(r2 + 3, 1) = Worksheets("site_user").Cells(r1 + 3, 1)
Exit For 'no point in doing the rest if we found a match
End If
Next r1
If Not found Then 'will colour a cell if there is a site with noone allocated to it
Worksheets("site_schedule").Cells(r2 + 3, 2).Interior.ColorIndex = 35
End If
Next r2
End Sub

As Im still learning Im keeping in the quotes to remind me when I go back to look, that and I've placed in a number of functions to populate certain cells for debug purposes. Apologies for the formatting. I didnt see a quote tab in the editor to make it look as it does in Excel.

Will update when I get the next bit working.

-CD

Im making progress slowly but surely. I have unfortunately hit a snag.

What I would like the script to do is to populate the users sheet with the scheduled task as it is going through and populating users to the site_schedule sheet. The name of the users sheet would vary depending on where it is in matching users to scheduled tasks. I thought I could use a variable in worksheets() to find the last row in the users sheet by using the following

Please Login or Register  to view this content.

Unfortunately it spits the dummy so to speak.

Current itteration of the code is as follows.

Please Login or Register  to view this content.

Thanks for the tip, incorporated the style into the script.

Im keen on using a variable as the "sheet1" for me would be a variable based on what user is being matched at that part of the script. All users sheets will be the users names. This way I thought that when a site is found to have a user, it would place the users name against the site in the site_schedule, and at the same time add the scheduled task to the users sheet.

I managed to do it using a further loop, though I know this could get a touch unwieldly if there were many users.

Please Login or Register  to view this content.

If there was a way I could use worksheets("variable_name_here") then I could cut the loop out.

Superb, thank you very much.

I was using the wrong Dim field. Changed to string and used the formula as you pointed out and it worked a charm.