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.

HansVogelaar 

Hello Hans,

I wonder if you were able to run this code. I ran it and it only put the drive count number in the 'DROPDOWLISTBOX' in whatever week was called. Nothing was copied to the 'Drive Count' worksheet for any week I ran.

I have a question about this statement;

' 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

There would not be any 'Team Names' in the 'Drive Count' worksheet until the data was filled.

fredc2022 

Thanks, I think I finally understand what you want.

The code below assumes that you have added the Drive Count worksheet to the workbook with DROPDOWNLISTBOX and DTIVEWORKSHEET.

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")
    ' Week
    wk = wt.Range("D2").Value
    m = wt.Range("I3").End(xlDown).Row
    For r = 4 To m
        ' Team name
        tm = wt.Range("I" & r).Value
        ' Find team
        Set rg = ws.Range("B:D").Find(What:=tm, LookAt:=xlPart, MatchCase:=False)
        If Not rg Is Nothing Then
            ' Find week
            Set rg = rg.Offset(1).EntireRow.Find(What:=wk, LookAt:=xlWhole, MatchCase:=False)
            If Not rg Is Nothing Then
                ' Drive
                dr = wt.Range("J" & r).Value
                ' Find drive
                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
                    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 data
                            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

See the attached version. Please check the results carefully.

HansVogelaar 

I apologize for the confusion. I have attached the 'DRIVE-COUNT-WS-WITH-DATA' that contains what the first week of games played would look like.
What we had at the begging were two worksheets. 1) DROPDOWMLISTBOX, 2) DRIVEWORKSHEET.
In cell 'D2' of the 'DROPDOWNLISTBOX' we have a dropdown list box with 22 weeks to choose from.
1) The macro takes that 'week #' and goes to the 'DRIVEWORKSHEET'. The 'DRIVEWORKSHEET' is set up as follows. the 'TEAM NAMES' are in column 'B:D' with the first 'TEAM NAME' in row 4. The other thirty-one (31) are in alphabetical order and are fifty-two (52) rows apart. For example: The second 'TEAM NAME' would be in 'B56:D56", the third would be in 'B108:D108', and the last would be in 'B1616:D1616'.
2) One row below (row 5) each 'TEAM NAME' has twenty-two (22) weeks from left to right with the first week in the fifth column (cell E5) and each successive week is ten (10) columns right. For example: week 1=E, week 2=O, week 3=Y, week 4=AI.
3) Once the 'TEAM NAME' and 'WEEK #' have been found then the 'DRIVE NAME' is found. The 'DRIVE NAME' will be offset 3 columns left from the 'WEEK #' and either one (1) or twenty-five (25) rows down. The row number is based on if the "TEAM NAME' being found is the home team or away team.
4) Once the 'DRIVE NAME' is found offset down six (6) rows to the first 'DRIVE #', Then drop down to the first blank cell and count back up the the original cell. For example: the 'ARIZONA CARDINALS', 'Week 1',  'DRIVE NAME' is in 'B6'. Drop down six (6) rows to the first drive 'B12', then drop down to the first black cell, 'B23' and count back up. The count is eleven (11) drives.

5) offset seven (7) columns right in the same range as the drive 'I12:I22' and count the 'Touchdowns and Field Goals.

6) Copy the 'TEAM NAME', the 'DRIVE COUNT', 'TOUCHDOWNS, AND FIELD GOALS' to the 'DRIVE COUNT' worksheet under the "HEADERS'. The 'WEEK #' are in row three (3) and the 'HEADERS' are in row four (4). For example: Week 1 is in column 'D', Week 2 is in column 'I', week 3 is in column 'N' 

The 'HEADERS' are in row four (4) and are in a four (4) column range with a blank column between each set of 'HEADERS'. For example: The first weeks 'HEADERS' are in range 'B4:E4', week 2 is in 'G4:J4', and week 3 is in 'L4:O4'. The date would be copied starting in row 5.

I hope that gives you a better understanding. Thank you for taking the time to help me with this problem.
 

fredc2022 

Could you attach a version of the Drive Count sheet with some dummy data? The one you posted earlier doesn't have any data whatsoever.

Or do I have to look at the DriveWorksheet? I'm getting more and more confused...

HansVogelaar 

Hello Hans,
Last time you helped I wanted to count the number of drives each team made per week throughout the 22-week season. That information came from the Driveworksheet, but it has data other than the number of drives. It also gives the results of those drives. Particularly, it tells you if the drive ended in a touchdown or a field goal. 

What I want to do now is again count the number of drives and also count the number of touchdowns and field goals. It appeared to me that capturing that data on another worksheet would be best. That worksheet is named Drive Count.
I think that you can read the macro code and see how we get to the Drives. From there to get to the touchdowns and field goals you would offset 7 columns right. And you would be looking at the same row numbers that the drives were counted. For example: if the drives were in the range B12:B22 the touchdowns and field goals would be in I12:I22.

I hope that explains what I want to do. I'll try to clarify any questions you have.

  

 

fredc2022 

OK, I have downloaded all the workbooks that you attached, but to be honest, I have no idea what you want.

fredc2022 

Hans,

I'm attaching the workbook with the code.
That might make it easier to work with.
I didn't include the Drive Count worksheet or
modify the Dropdownlistbox worksheet.

HansVogelaar

Hans,

Here's the new modified Dropdownlistbox 

fredc2022 

Thanks. Do you really want to use three separate workbooks, instead of three worksheets in a single workbook?

HansVogelaar