Forum Discussion
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 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
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.
For example:
Cells.Find(What:=Worksheets("Sheet1").Range("B2").Value, After:=ActiveCell, LookIn:= _
xlFormulas2, LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:= _
xlNext, MatchCase:=False, SearchFormat:=False).Activatewhere Sheet1 is the name of the sheet with the drop-downs, and B2 is the cell with the drop-down containing (among others) DALLAS COWBOYS.
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 Sub
