Forum Discussion
Solved
VBA Find (What:="") tied to dropdown list box
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 w...
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.
What I have done with the new attached file is remove the third drop down list box. In it's
place I put in a Vlookup(B2,I4:J35,2). Now, the third box named "Drives" is in sync with
the first box named "TEAMNAME". So, when you choose a TEAMNAME "Arizona Cardinals" the
DRIVES will automatically populate the DRIVES box with "Cardinals Drives."
When the macro runs it finds the number of drives (12) and places it in Sheet "DROPDOWNLISTBOX"
in cell "J4", LAST LINES "ActiveCell.Copy Worksheets("DROPDOWNLISTBOX").Range("L4")".
Question 1) Can the macro be automated to to run thru all the team names at once, find the number
of drives and place that number in Sheet DROPDOWNLIST BOX column (L4:L35)?
It appears to me that the drop down list box can't be used. I would probably have to use
the list in column (I4:I35) and run a loop so the macro would run, post the drive number and
then loop down to the next name.
Question 2) If that's possible then column "L" would need to be dynamic so the row number
would change. Maybe and offset from the team name to get the .Range )"Column/row number").
I thank you for your time and help.
Sub Find_TeamName_Week_Drives()
Cells.Find(What:=Worksheets("DROPDOWNLISTBOX").Range("B2").Value, After:=ActiveCell, _
LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
Cells.Find(What:=Worksheets("DROPDOWNLISTBOX").Range("D2").Value, After:=ActiveCell, LookIn:=xlValues, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
ActiveCell.Offset(0, -3).Select
Cells.Find(What:=Worksheets("DROPDOWNLISTBOX").Range("F2").Value, After:=ActiveCell, LookIn:= _
xlValues, 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
ActiveCell.Copy Worksheets("DROPDOWNLISTBOX").Range("L4")
End Sub
I ran into a couple of inconsistencies:
- There were some teams with two consecutive spaces on the second sheet; I changed those to single spaces.
- The Washington team name was different between the two sheets; I corrected that.
The macro in the attached workbook loops through the teams and fills the column for the selected week.
It would also be possible to loop through the weeks if desired.
- Hello Hans,
I opened the above file but code was the same as before. There's no loop in it. Maybe it didn't save. I hope you still have it an can repost. If it doesn't save maybe you could copy it to the post and I'll copy it to my worksheet.
Thanks for all you time and work. Have a great weekend.Sorry if I messed it up. Here is the modified version:
Sub Find_TeamName_Week_Drives() 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 n As Long Application.ScreenUpdating = False Set ws = Worksheets("DRIVEWORKSHEET") Set wt = Worksheets("DROPDOWNLISTBOX") 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 ' Week wk = wt.Range("D2").Value ' 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("F2").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 ' 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 End If End If End If Next r Application.ScreenUpdating = True End SubI have attached the workbook.
Thanks you taking the time to repost your macro. And please, don't be sorry about anything. I just appreciate the help. As you can tell,
I lack the knowledge to understand, let alone to write a competent macro.I have gone through the "Drive" worksheet and have corrected the offset spacing for the "Weeks" to be -3 on all. I don't think that would change
anything as it was for weeks 7, 8 and 12.I have ran the macro using the F8 but was unable to see where each step was in the worksheet. In the simple macros I make I'm able to see
each step.When I run the macro using Arizona Cardinals in the dropdown list box (B2) values are placed in Kansas City and Los Angeles Chargers "Weeks"
columns "L". I'm not sure why that is happening. Looking at those two teams drive numbers they do not match the actual numbers.Is there a way to watch, such as "step in" to see what the macro is doing?