SOLVED

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

Brass Contributor

In the VBA Find(What:=), I want the What:=" " to get it's value from a drop down list box
in sheet1. I have two worksheets, 1) sheet with three dropdown list boxes 2) sheet with unstructured
data where the data from dropdown box wiil be found. These are the VBA codes I have.
Thank you for any help ou can give.

Cells.Find(What:="DALLAS COWBOYS", After:=ActiveCell, LookIn:= _
xlFormulas2, LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:= _
xlNext, MatchCase:=False, SearchFormat:=False).Activate

Cells.Find(What:="WEEK 3", After:=ActiveCell, LookIn:=xlFormulas2, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
ActiveCell.Offset(0, -3).Select

Cells.Find(What:="Cardinals Drives", After:=ActiveCell, LookIn:= _
xlFormulas2, LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:= _
xlNext, MatchCase:=False, SearchFormat:=False).Activate
'Range(ActiveCell).Select
ActiveCell.Offset(5, 0).Select
Range(ActiveCell, ActiveCell.End(xlDown)).Select
Selection.End(xlDown).Select

46 Replies
Hello Hans,
Hope ou are doing well.
I would like to review this thread that you helped me with last year,
I would like to modify the macro. We were using two worksheets and I would like to go the 3 worksheets. the new worksheet is called 'Drive Count'.
The first worksheet, "Dropdownlistbox" used cell 'D2' to call a week # from 1 to 22. Then go to the 'Driveworksheet' to count the number od drives and copy that number back to the 'Dropdownlistbox" worksheet.
Now I want to count the number of 'touchdowns' and 'fieldgoals' along with the 'number of drives'.
Are you able to use this links to see the worksheets?
DROPDOWNLISTBOX WORKSHEET
https://imgur.com/KetyDKZ

DRIVEWORKSHEET
https://imgur.com/bj5QX6i

DRIVE COUNT WORKSHEET
https://imgur.com/q342Efm
Thanks for your consideration.

@fredc2022 

Hi Fred, those are just screenshots of the worksheets. Could you attach a copy of the workbook itself?

@fredc2022 

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

Hello Hans,
I made a macro to make the Drive Count worksheet and did that just because there was going to be more data to capture. I still run the macro to populate the Dropdownlistbox range I3:ac35 for the weeks. We could take the headers from the Drive Count worksheet and copy them down to row 40 starting in column B in the Dropdownlistbox. That way we just use two worksheets.
Hans,
After looking at the change I suggested that's not going to work. So, let's just put those headers in range AE3:EJ4

@Hans Vogelaar

 

Hans,

Here's the new modified Dropdownlistbox 

@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.

@fredc2022 

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

@Hans Vogelaar 

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.

  

 

https://imgur.com/1HKgCaS

I thought I would post this screenshot to show the data area in the above example.

@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...

@Hans Vogelaar 

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 

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.

@Hans Vogelaar 

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 

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.

I wasn't able to download the link you provided at first but I have now and I see the code works.
I then copied the TeamNames to my Drive Count worksheet and ran the code. However, it did not run but did put the counted number of drives in the DROPDOWNLISTBOX.

WOULD IT BE POSSIBLE TO HAVE THE CODE PUT THE TEAM NAME IN DRIVE COUNT WORKSHEET FOR EACH WEEK?

I checked to make sure the Drive Count was spelled correctly and that I had no space at the beginning or end. I'm not sure why my original is not working with the code. Please tell me if you can, if I delete the Drive Count worksheet and then add that same named sheet back, would that cause the code to have a problem running?

Anyway, I thank you for your help in modifying the code. I worked with it but just could get it to work but I knew you could. Your knowledge is fabulous along with your graciousness to help others. Thanks again! Keep up your good work.

@fredc2022 

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

@Hans Vogelaar 

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 

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?