VBA to Loop through Two Data Validation Lists

[MrJoshua]

Hi hopefully someone out there can help me.

I have a Spreadsheet created/maintained by someone else with 2 data validation drop-downs in cells G2 and I2...one for Instructor Name and one for Student Name which loads the students scores. Because of the way the other person has the spreadsheet laid out...I have not been able to arrive a the correct scores using functions like INDEX(MATCH...or a Pivot Table. I have to manually select an Instructor Name then each Students Name assigned to that Instructor...Select the next Instructor Name...each of their Students and so on to pull the scores for each and every Student...and for each student copy cell E6 and past to the next open space in Column AB starting in Row 1 (AB1).

I have been working on the code below which successfully selects the first Instructor and all their Students...it will successfully switches to the next Instructors but the data validation source (which is using an INDIRECT formula to pull the list associated with Instructor) on the Student drop down doesn't appear to refresh so it just cycles through the list of Students for the first Instructor again.

Any Ideas...?

Sub AutomateStudentScoring()
'
' AutomateStudentScoring Macro
'

    Dim dvCella As Range
    Dim dvCellb As Range
    Dim inputRangea As Range
    Dim inputRangeb As Range
    Dim a As Range
    Dim b As Range
    Dim i As Long
     
     'Which cell has data validation
    Set dvCella = Worksheets("Coaching").Range("G2")
    Set dvCellb = Worksheets("Coaching").Range("I2")
     'Determine where validation comes from
    Set inputRangea = Evaluate(dvCella.Validation.Formula1)
    Set inputRangeb = Evaluate(dvCellb.Validation.Formula1)
     
    i = 1
     'Begin our loop
    Application.ScreenUpdating = False
    For Each a In inputRangea
        dvCella = a.Value
            For Each b In inputRangeb
            dvCellb = b.Value
            i = i + 1
            Worksheets("Coaching").Cells(i, "AB").Value = Worksheets("Coaching").Range("E6").Value
            Next b
        Next a
    Application.ScreenUpdating = True
    
End Sub

 

[tonyyy]

MrJoshua,

If dvCellb is dependent on dvCella, then you might try moving the "Set inputRangeb..." statement.

 'Begin our loop
Application.ScreenUpdating = False
For Each a In inputRangea
    dvCella = a.Value
    [COLOR=#ff0000]Set inputRangeb = Evaluate(dvCellb.Validation.Formula1)[/COLOR]
    For Each b In inputRangeb
        dvCellb = b.Value
        i = i + 1
        Worksheets("Coaching").Cells(i, "AB").Value = Worksheets("Coaching").Range("E6").Value
    Next b
Next a

Cheers,

tonyyy