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

HansVogelaar 

Hans, kudos to you for another GREAT JOB!

If I only had part of your knowledge. 

Again, thank you so much for your help, not only for myself but all the others you help.

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

HansVogelaar 
I'll try to clarify this as best I can and hope I don't confuse you more.
Here is your original code. This code works with two worksheets, 1) DROPDOWNLISTBOX, AND 2) DRIVEWORKSHEET.  The DRIVEWORKSHEET holds the data that I want to find. The

DROPDOWNLISTBOX has a drop-down list box with weeks 1 thru 22 to pick from. The worksheet also holds in column (I) the Team Names and in column (J) the Drive Names of each team. It also has the original range where the Drive Numbers counted would be copied. That range is (L4:Ag35). Above that range are Week Number headers, Week 1 thru Week 22. The code below accomplished that.

I have added a Home (AL4:BG35) and an Away (BK4:CF35) range in the DROPDOWNLISTBOX with the same headers for Week 1 thru Week 22 for both.

What I want to do now is the same thing but I want to sort the drives into Home and Away. And to do that we need to look at the structured layout of the data worksheet DRIVEWORKSHEET. The original code does several lookups, 1) finds the Team Name, 2) xldowns I row to find the Week Number, then offsets 3 columns left to find the Drive Names. The Drive Name for the Team Name being looked up could be in either one row down (Home) or 25 rows down (Away).

And that is where I need your help.  Modify the code so when the Drive Name is found, then determine if it's Home (row 1)or Away (row 25) and populate the drive count number into the proper range.

Sub FIND_TEAMNAME_WEEK_DRIVES2()
Dim ws As Worksheet
Dim wt As Worksheet
Dim wu 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 n As Long
Dim nt As Long
Dim nf As Long
Dim rc As Range
Dim u As Long
Dim v As Long
Application.ScreenUpdating = False
Set ws = Worksheets("DRIVEWORKSHEET")
Set wt = Worksheets("DROPDOWNLISTBOX")
Set wu = Worksheets("Drive Count")

' Get Call Week Number from DROPDOWNLISTBOX COLUMN D2
wk = wt.Range("D2").Value
m = wt.Range("I3").End(xlDown).Row
For r = 4 To m

' Get Team Name from DROPDOWNLISTBOX COLUMN I
tm = wt.Range("I" & r).Value

' Find Team Name from DRIVEWORKSHEET
Set rg = ws.Range("B:D").Find(What:=tm, LookAt:=xlPart, MatchCase:=False)
If Not rg Is Nothing Then

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

' Find Drive Name from DROPDOWNLISTBOX column J
dr = wt.Range("J" & r).Value

' Find Drive Name on DRIVEWORKSHEET
Set rg = rg.Offset(0, -3)
Set rg = rg.EntireColumn.Find(What:=dr, After:=rg, LookAt:=xlWhole, MatchCase:=False)
If Not rg Is Nothing Then

' Get values (Count) for Touchdowns, and Field Goals from DRIVEWORKSHEET
n = rg.Offset(5).End(xlDown).Value

' Reset touchdown count and field goal count
nt = 0
nf = 0

' Count touchdowns and field goals
For Each rc In rg.Offset(6, 7).Resize(n)
Select Case rc.Value
Case "Touchdown"
nt = nt + 1
Case "Field Goal"
nf = nf + 1
End Select
Next rc

' Find Week on DROPDOWNLISTBOX
Set rg = wt.Range("3:3").Find(What:=wk, LookAt:=xlWhole, MatchCase:=False)
If Not rg Is Nothing Then

' Populate cell
wt.Cells(r, rg.Column).Value = n
End If

' Find team on Drive count
Set rg = wu.Range("B:B").Find(What:=tm, LookAt:=xlWhole, MatchCase:=False)
If Not rg Is Nothing Then
u = rg.Row

' Find week on Drive Count
Set rg = wu.Range("3:3").Find(What:=wk, LookAt:=xlWhole, MatchCase:=False)
If Not rg Is Nothing Then
v = rg.Column

' Fill Drives, Touchdowns,and Field Goals in DRIVE COUNT WORKSHEET
wu.Cells(u, v - 1).Value = n
If nt > 0 Then
wu.Cells(u, v).Value = nt
End If
If nf > 0 Then
wu.Cells(u, v + 1).Value = nf
End If
End If
End If
End If
End If
End If
Next r
Application.ScreenUpdating = True
End Sub

fredc2022 

I composed my previous reply while you added a new reply. I haven't read that one yet...

fredc2022 

I have no idea what your code tries to do.

The line

If IsNumeric(n) Then

is useless - n is ALWAYS numeric, as far as I can tell.

And in the line

If rg.Row = 1 Then

rg.Row will NEVER be 1, for rg is the range containing the drive, i.e. in row 6, 30, etc.

HansVogelaar 
Hello Hans,
I hope you are doing and Thank You so much for all your help.

I'm still working in the same worksheets and workbook.

This is the DROPDOWNLISTBOX again and you figured out the code to search the DRIVE count data from the DRIVEWORKSHEET. We look that data by first looking in cell 'D2' of the 'DROPDOWNLISTBOX' where we have a dropdown list box with weeks 1 thru 22 to choose from. We then go to the "DRIVEWORKSHEET' find the 'Team Name', 'Week Number', 'Drive Name' then count the number of drives for each 'Team Name'. We then copied the 'Drive Count Number' in a range 'L4:AG35' in the 'DROPDOWNLISTBOX' under the week number headers in row 3.

What I want to do now is modify the same formula to find 'Home' or 'Away' Drive count and place those 'Drive Numbers' in the new ranges (Home 'AL4:BG35' and 'Away 'BK4:CF35") in the same 'DROPDOWNLISTBOX'. Here's how I've modified the code: 
I'm getting 'mismatch errors with this part:
awayRange.Cells(r, awayRange.Column + (wk - 1)).Value = countValue

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 homeRange As Range
Dim awayRange As Range
Dim n As Variant

Application.ScreenUpdating = False

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

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

' Set the HOME and AWAY fill data areas
Set homeRange = wt.Range("AL4:BG35")
Set awayRange = wt.Range("BK4: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 rg = rg.EntireColumn.Find(What:=dr, After:=rg, LookAt:=xlWhole, MatchCase:=False)

If Not rg Is Nothing Then

' Get value of Drives Count from DRIVEWORKSHEET
n = rg.Offset(5).End(xlDown).Value
Debug.Print "Drive Count: " & n

' Convert n to a numeric type before assigning to the cell value
Dim countValue As Double
If IsNumeric(n) Then
countValue = CDbl(n)

If rg.Row = 1 Then

' It is a home data

' Put the count data in the HOME range
homeRange.Cells(r, homeRange.Column + (wk - 1)).Value = countValue
Debug.Print "Putting count in HOME range"
Else

' It is an away data

' Put the count data in the AWAY range
awayRange.Cells(r, awayRange.Column + (wk - 1)).Value = countValue
Debug.Print "Putting count in AWAY range"
End If
Else

' Handle the case where n is not a valid numeric value

'countValue = 0 ' or any default value you prefer
End If
End If
End If
End If
Next r

Application.ScreenUpdating = True
End Sub

I've dropped the worksheets below for your review. I hope you can, again, straighten me out. Please let me know if there is anything I have left out.

fredc2022 

That makes no sense to me. You already have the week headers on that sheet, so why not have the team names there in advance too?

HansVogelaar 

Hans,

Your code works just fine.

Is there a way to have the code automatically fill in the Team Names each week without doing it manually?

fredc2022 

I am sorry, I don't know why the code still not works as you want. I'm out of ideas.

fredc2022 

You have to place the list of team names in column B of the Drive Count sheet, just like you have that list in column I of the DROPDOWNLISTBOX sheet.

The list was there in your sample sheet, so the code worked.