Forum Discussion
VBA Find (What:="") tied to dropdown list box
- May 02, 2022
Hi Fred,
I see the mistake that I made. I hope that the attached version works correctly. You'll notice that there is no Teams dropdown anymore. It isn't needed because the macro loops through all teams.
You only need to select a week, then click the button.
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.
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.