@Hans Vogelaar
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
