لجان الدور الثاني حسب المادة وحسب الجنس

Option Explicit



Sub Get_data()

Application.EnableEvents = False

  Dim s_rg As Range, find_rg As Range

  Dim Mou3addaL#, last_Clas%, I%, m%, col%

  Dim Mal_Femal$

  Dim First_sheet As Worksheet

  Dim Second_sheet As Worksheet

  Dim Position%, k%

  Position = 19

  Set First_sheet = Sheets("ف.1.أ")

  Set Second_sheet = Sheets("اللجنة")

    Mal_Femal$ = Second_sheet.Cells(1, 7)

  For I = 4 To 300 Step Position

    If Second_sheet.Cells(I - 1, 2) = "" Then Exit For

    Second_sheet.Cells(I, 2).Resize(15, 3).ClearContents

  Next

If Mal_Femal = "" Then Exit Sub



last_Clas = First_sheet.Cells(Rows.count, 2).End(3).Row

Set find_rg = First_sheet.Rows(4).Find(Second_sheet.Cells(1, 6), lookat:=1)

If find_rg Is Nothing Then Exit Sub

col = find_rg.Column + 8

Mou3addaL = Val(First_sheet.Cells(8, col)) / 2

m = 4: k = 1

 For I = 10 To last_Clas

  If First_sheet.Cells(I, col) < Mou3addaL And _

     First_sheet.Cells(I, 4) = Mal_Femal Then

        If m Mod 19 = 0 Then m = m + 4: k = 1

          With Second_sheet.Cells(m, 2)

            .Value = k

            .Offset(, 1) = First_sheet.Cells(I, 3)

            .Offset(, 2) = First_sheet.Cells(I, 6)

          End With

     m = m + 1: k = k + 1

   End If

Next

  Application.EnableEvents = True

End Sub