Je veux juste demander si comment puis-je raccourcir les codes ci-dessous? Les codes fonctionnent bien, mais je veux juste savoir s'il existe un moyen de le raccourcir puisque j'utiliserai les codes de la feuille N-Q1 dans d'autres feuilles de quart (N-Q1, N-Q2, N-Q3 , N-Q4, JK-Q1, etc.). Notez que les quarter sheets ont la même structure ou colonne (colonne 16) à mettre à jour, tandis que la feuille STUDENTS_INFO est dans la colonne 20 .

Feuilles INFO ÉTUDIANTS :

Set ws = ActiveWorkbook.Worksheets("STUDENTS_INFO")
lastRow = ws.Cells(Rows.Count, "C").End(xlUp).Row
        
    For r = 9 To lastRow
        
        If ws.Cells(r, 3) = CStr(ThisWorkbook.Sheets("HOME").Range("K11").value) Then
            If ws.Cells(r, 20) = "0" Or ws.Cells(r, 20) = "" Then
                ws.Cells(r, 20) = "1"
                Debug.Print "STUDENTS: " & ws.Cells(r, 3) & "  Verified!"
            Else
                Debug.Print "STUDENTS: " & ws.Cells(r, 3) & "  Already Verified!"
            End If
        End If
    
    Next r

Code N-Q1 : Quarter Sheets (N-Q1, N-Q2, N-Q3, N-Q4, JK-Q1, etc.)

    grd = ThisWorkbook.Sheets("HOME").Range("K16").value
    qrt = CStr(ThisWorkbook.Sheets("HOME").Range("K17").value)
            
    If grd = "Nursery" Then
        ws_output = "N" + "-" + qrt
    ElseIf grd = "Junior Kinder" Then
        ws_output = "JK" + "-" + qrt
    ElseIf grd = "Senior Kinder" Then
        ws_output = "SK" + "-" + qrt
    End If
    
    Set ws = ActiveWorkbook.Worksheets(ws_output)
    lastRow = ws.Cells(Rows.Count, "C").End(xlUp).Row
        
    For s = 9 To lastRow
        
        If ws.Cells(s, 3) = CStr(ThisWorkbook.Sheets("HOME").Range("K11").value) Then
            If ws.Cells(s, 16) = "0" Or ws.Cells(r, 16) = "" Then
                ws.Cells(s, 16) = "1"
                Debug.Print "GRADES: " & ws.Cells(s, 3) & "  Verified!"
            Else
                Debug.Print "GRADES: " & ws.Cells(s, 3) & "  Already Verified!"
            End If
        End If
    
    Next s

Notez que ce code ne fonctionnera qu'en fonction de la valeur de grd et qrt.

Je pense utiliser le code ci-dessous pour commencer.

For Each ws In Sheets(Array("STUDENTS_INFO", "N-Q1", "N-Q2", "N-Q3", "N-Q4", "N-D", _
"JK-Q1", "JK-Q2", "JK-Q3", "JK-Q4", "JK-D", "SK-Q1", "SK-Q2", "SK-Q3", "SK-Q4", "SK-D"))
    
        With ws.Cells(8, 3).CurrentRegion
            .AutoFilter 2, LRN
            ws.AutoFilterMode = False
        End With

Next ws
0
cjvdg 12 mars 2021 à 09:00

1 réponse

Meilleure réponse

Veuillez consulter le code ci-dessous pour la solution que j'ai proposée. Merci!

For Each ws In Sheets(Array("N-Q1", "N-Q2", "N-Q3", "N-Q4", "N-D", _
    "JK-Q1", "JK-Q2", "JK-Q3", "JK-Q4", "JK-D", "SK-Q1", "SK-Q2", "SK-Q3", "SK-Q4", "SK-D"))
    
    lastRow = ws.Cells(Rows.Count, "C").End(xlUp).Row
        
    For r = 9 To lastRow
        
        If ws.Cells(r, 3) = CStr(ThisWorkbook.Sheets("HOME").Range("K11").value) Then
            If ws.Cells(r, 16) = "0" Or ws.Cells(r, 16) = "" Then
                ws.Cells(r, 16) = "1"
                Debug.Print "STUDENTS: " & ws.Cells(r, 3) & "  Verified!"
            Else
                Debug.Print "STUDENTS: " & ws.Cells(r, 3) & "  Already Verified!"
            End If
        End If
    
    Next r
    
    Next ws
0
cjvdg 15 mars 2021 à 01:05