VBA Find (What:="") tied to dropdown list box

fredc2022 

Thanks, I think I finally understand. Se if this version does what you want:

Sub FIND_TEAMNAME_WEEK_DRIVES_HOME_AND_AWAY_ED7_15MAY23()
    Dim ws As Worksheet
    Dim wt As Worksheet
    Dim r As Long
    Dim m As Long
    Dim tm As String
    Dim wk As String
    Dim dr As String
    Dim rg As Range
    Dim rg2 As Range
    Dim homeRange As Range
    Dim awayRange As Range
    Dim n As Variant
    Dim c As Long

    Application.ScreenUpdating = False

    Set ws = Worksheets("DRIVEWORKSHEET")
    Set wt = Worksheets("DROPDOWNLISTBOX")

    ' Get Call Week in DROPDOWNLISTBOX cell D2
    wk = wt.Range("D2").Value
    c = Split(wk)(1)
    m = wt.Range("I3").End(xlDown).Row

    ' Set the HOME and AWAY fill data areas
    Set homeRange = wt.Range("AL1:BG35")
    Set awayRange = wt.Range("BK1:CF35")
    Debug.Print wt.Range("AL4:BG35").Address
    Debug.Print wt.Range("BK4:CF35").Address
    For r = 4 To m
        ' Get Team Name from DROPDOWNLISTBOX column I
        tm = wt.Range("I" & r).Value
        Debug.Print "Team Name: " & tm

        ' Find Team Name from line above in the DRIVEWORKSHEET
        Set rg = ws.Range("B:D").Find(What:=tm, LookAt:=xlPart, MatchCase:=False)

        If Not rg Is Nothing Then
            ' Find the Call Week Number from DROPDOWNLISTBOX cell D2 in DRIVEWORKSHEET
            Set rg = rg.Offset(1).EntireRow.Find(What:=wk, LookAt:=xlWhole, MatchCase:=False)

            If Not rg Is Nothing Then
                ' Get the Drive Name from the DROPDOWNLISTBOX column J Find in the DRIVEWORKSHEET
                dr = wt.Range("J" & r).Value
                Debug.Print "Drive Name: " & dr

                ' Find Drive Name in the DRIVEWORKSHEET
                Set rg = rg.Offset(0, -3)
                Set rg2 = rg.EntireColumn.Find(What:=dr, After:=rg, LookAt:=xlWhole, MatchCase:=False)

                If Not rg2 Is Nothing Then
                    ' Get value of Drives Count from DRIVEWORKSHEET
                    n = rg2.Offset(5).End(xlDown).Value
                    Debug.Print "Drive Count: " & n

                    If rg2.Row - rg.Row = 1 Then
                        ' It is a home data
                        ' Put the count data in the HOME range
                        homeRange.Cells(r, c).Value = n
                        Debug.Print "Putting count in HOME range"
                    Else
                        ' It is an away data
                        ' Put the count data in the AWAY range
                        awayRange.Cells(r, c).Value = n
                        Debug.Print "Putting count in AWAY range"
                    End If
                End If
            End If
        End If
    Next r

    Application.ScreenUpdating = True
End Sub