Forum Discussion
VBA Find (What:="") tied to dropdown list box
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.
Hans,
Thank you for your reply and solution to my question. It works now!
Now I would like to make it go thru all team at one time. That would require tagging the drive name to the team name. Ex: Dallas Cowboys name box tied to the Cowboys Drives name box. When the VBA runs it comes up with a number and that number this is placed into the lookup week.
I'll see if I can put the worksheets in a smaller workbook so you can see what I'm talking about.
Again, thanks for your help.
I'm afraid I don't quite understand what you want. Could you try to explain in a more specific way? Thanks in advance.
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 SubI 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.
