Excel vba loop through list until

[Andyztes]

Hello,
I am trying to work on a way to automatically fill in a schedule for where someone should be. What I have so far is in my 1st sheet I have a table of cells I want to fill, across the columns i have dates, and down the rows I have the locations. In my 2nd sheet I have the dates of when people are working going out three weeks. In my 3rd I have a list of names for the various locations in order of priority, I also have these as named ranges. What I would like to do is in the first blank cell of the table "B3" use the corresponding named range and use the first name in the list and check if it is in the other list(2nd sheet) for that date. If a name matches put that name in that cell and end loop, if not go to the next name until a name is used. while finding a name if also should check the other locations making sure that name isn't somewhere else if so then move to next name.

I don't know if this is possible I assume it is since excel can do it all, but I'm not sure on how to go about doing it. I think it might have to be in a do until loop or a for loop or a combination of the two. any sort of lead would be greatly appreciated thanks in advance.

 

[Andyztes]

This is what i got so far its not working of course but maybe on the right track?

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim N As Range
Dim xlCell As Range
Dim sh1 As Worksheet
Dim sh1r As Range
Dim sh2 As Worksheet
Dim sh2r As Range

Set sh1 = ActiveWorkbook.Worksheets("Sheet2")
Set sh1r = sh1.Range("B4:B39")
Set sh2 = ActiveWorkbook.Worksheets("Sheet1")
Set sh2r = sh2.Range("B3")

Do While Worksheets("Sheet1").Range("B3") Is Nothing
For Each N In Range("Controls")
For Each xlCell In sh1r
If xlCell.Value = N Then
sh2r = N
End If
Next xlCell
Next N
Loop
End Sub

 

[Andyztes]

this is currently where im at, it seems to sort of works, it doesn't seem to take the first name in my named range and check if its in the range in sheet 2. it seems to just find a match and display in the given cell. (Skipping the first two people)
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim N As Range
Dim xlCell As Range
Dim sh1 As Worksheet
Dim sh1r As Range
Dim sh2 As Worksheet
Dim sh2r As Range
Dim val
Dim ws As Worksheet

Set ws = Worksheets("Sheet3")
Set sh1 = ActiveWorkbook.Worksheets("Sheet2")
Set sh1r = sh1.Range("B4:B39")
Set sh2 = ActiveWorkbook.Worksheets("Sheet1")
Set sh2r = sh2.Range("B8")

Do While sh2r = ""
For Each N In ws.Range("Controls")
val = N
For Each xlCell In sh1r
If xlCell.Value = val Then
sh2r = val
End If
Next xlCell
Next N
Loop

End Sub

 

[Rick Rothstein]

I think you might have a better chance of getting a reply if you post (coordinated) samples of your three worksheets so we can see how they are laid out and how the data on worksheets 2 and 3 end up looking on the first worksheet. You can use DropBox to post your samples.

 

[Andyztes]

[TABLE="class: grid, width: 200"]
<tbody>[TR]
[TD]PM Schedule
[/TD]
[TD]10/30/17
[/TD]
[TD]10/31/17
[/TD]
[TD]11/1/17
[/TD]
[/TR]
[TR]
[TD]Controls
[/TD]
[TD]JANE
[/TD]
[TD]BRUNILDA
[/TD]
[TD]JANE
[/TD]
[/TR]
[TR]
[TD]Dosis
[/TD]
[TD]TERESA
[/TD]
[TD]TERESA
[/TD]
[TD]KIM E
[/TD]
[/TR]
[TR]
[TD]Fridge
[/TD]
[TD]GERISE
[/TD]
[TD]KIM
[/TD]
[TD]GERISE
[/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="class: grid, width: 200"]
<tbody>[TR]
[TD]10/30/17
[/TD]
[TD]10/31/17
[/TD]
[TD]10/1/17
[/TD]
[/TR]
[TR]
[TD]JANE
[/TD]
[TD]KIM E
[/TD]
[TD]JANE
[/TD]
[/TR]
[TR]
[TD]GERISE
[/TD]
[TD]KIM
[/TD]
[TD]KIM E
[/TD]
[/TR]
[TR]
[TD]TERESA
[/TD]
[TD]BRUNILDA
[/TD]
[TD]GERISE
[/TD]
[/TR]
[TR]
[TD]ROBIN
[/TD]
[TD]TERESA
[/TD]
[TD]LISA
[/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="class: grid, width: 200"]
<tbody>[TR]
[TD]Contols
[/TD]
[TD]Dosis
[/TD]
[TD]Fridge
[/TD]
[/TR]
[TR]
[TD]JANE
[/TD]
[TD]TERESA
[/TD]
[TD]DANIELLE
[/TD]
[/TR]
[TR]
[TD]ROBIN
[/TD]
[TD]JOSEPH
[/TD]
[TD]GERISE
[/TD]
[/TR]
[TR]
[TD]GERISE
[/TD]
[TD]KIM E
[/TD]
[TD]KIM
[/TD]
[/TR]
[TR]
[TD]BRUNILDA
[/TD]
[TD]KIM
[/TD]
[TD]LISA
[/TD]
[/TR]
</tbody>[/TABLE]

This is basically the layout I have, the first table is where I want to fill in the names. the second table is the dates people are people are working. The third table is where I have my named ranges. What I would like to do is take the first name in "Controls" and see if they are working on that date, if they are fill in the name, if not then move to the next name. I would also like it to check if the name was used already for that date. I filled the first table in with how the names should fill in.

 

[Andyztes]

Currently my code, seems to sort of work it doesn't seem to take the first name in the named range and test it, almost seems like it see if a name is in both and uses that. Also it doesn't check the names in the other locations of that date to see if they are already used.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Dim N As Range
    Dim xlCell As Range
    Dim sh1 As Worksheet
    Dim sh1r As Range
    Dim sh2 As Worksheet
    Dim sh2r As Range
    Dim ws As Worksheet
    Dim D As Range
    
    Set ws = Worksheets("Sheet3")
    Set sh2 = ActiveWorkbook.Worksheets("Sheet1")
    Set sh1 = ActiveWorkbook.Worksheets("Sheet2")
    
    Do While sh2.Range("B8") = ""
        
        Set sh1r = sh1.Range("B4:B39")
        Set sh2r = sh2.Range("B8")
        For Each xlCell In sh1r
            For Each N In ws.Range("Controls")
                If xlCell.Value = N Then
                    sh2r = N
                End If
            Next N
        Next xlCell
    Loop