Apr 27 2022 05:07 PM
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
Apr 23 2023 05:30 PM
Apr 24 2023 12:52 AM
Hi Fred, those are just screenshots of the worksheets. Could you attach a copy of the workbook itself?
Apr 24 2023 02:29 PM
Apr 24 2023 02:43 PM
Thanks. Do you really want to use three separate workbooks, instead of three worksheets in a single workbook?
Apr 24 2023 03:42 PM
Apr 24 2023 04:03 PM
Apr 24 2023 04:10 PM
Apr 24 2023 06:43 PM
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.
Apr 25 2023 04:29 AM
OK, I have downloaded all the workbooks that you attached, but to be honest, I have no idea what you want.
Apr 25 2023 01:56 PM
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.
Apr 25 2023 02:09 PM
Apr 25 2023 02:15 PM - edited Apr 25 2023 02:19 PM
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...
Apr 25 2023 09:25 PM
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.
Apr 26 2023 11:09 AM
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.
Apr 26 2023 02:27 PM
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.
Apr 26 2023 02:41 PM
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.
Apr 26 2023 04:05 PM
Apr 27 2023 01:08 AM
I am sorry, I don't know why the code still not works as you want. I'm out of ideas.
Apr 27 2023 09:43 AM
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?
Apr 27 2023 10:07 AM
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?