VBA Find (What:="") tied to dropdown list box

[fredc2022]

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

[#2]

@fredc2022 

For example:

Cells.Find(What:=Worksheets("Sheet1").Range("B2").Value, After:=ActiveCell, LookIn:= _
xlFormulas2, LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:= _
xlNext, MatchCase:=False, SearchFormat:=False).Activate

where 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.

[#3]

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.

[#4]

@fredc2022 

I'm afraid I don't quite understand what you want. Could you try to explain in a more specific way? Thanks in advance.

[#5]

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

[#6]

@fredc2022 

I ran into a couple of inconsistencies:

1. There were some teams with two consecutive spaces on the second sheet; I changed those to single spaces.
2. 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.

[#7]

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.

[#8]

@fredc2022 

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 Sub

I have attached the workbook.

[#9]

@Hans Vogelaar 

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?

[#10]

@fredc2022 

Press Alt+F11 to activate the Visual Basic Editor.

If you don't see the code immediately, click the + buttons in the tree view at the left until you see Module1, then double-click Module1.

Click anywhere in the macro code.

Each time you press F8, one step will be executed.

You can view the value of the variables used in the code  by hovering the mouse pointer over them.

[#11]

Hello Hans,
Thanks for the cursor tip. Didn't know that. Still don't under stand why I can't see the cell that the code is referring to. I'm assuming that is because it doesn't call for "select" or "activate." Is that correct?

Please advise where this is in the "DROPDOWNLISTBOX";
' Find week on DROPDOWNLISTBOX
Set rg = wt.Range("3:3").Find(What:=wk, LookAt:=xlWhole, MatchCase:=False)
Not sure where the cell is unless it's starts at "A1". I don't see where that gets us.
I'm playing with the code to see where each piece is on the worksheet.

Thanks for hanging in with me. I'm going to try to be up at your post times tonight.

[#12]

@fredc2022 

The code intentionally does not select cells, because a macro runs much more efficiently if it does not.

The line

Set rg = wt.Range("3:3").Find(What:=wk, LookAt:=xlWhole, MatchCase:=False)

looks for the week in row 3. Since we don't specify where to begin, it starts from the first cell in row 3, i.e. A3.

I'm going to single-step through the macro again to see if I can detect an error.

[#13]

Hans,
A second thought to clean up my worksheet. Where in the worksheet was this that you fixed?
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."

[#14]

@fredc2022 

Cell B56 contained "ATLANTA  FALCONS" with 2 spaces between ATLANTA and FALCONS.

Since the other sheet contains "ATLANTA FALCONS" with one space, the code couldn't find it.

I thought there was another one, but I couldn't find it now.

There were other cells that looked empty but contained (only) two spaces; those don't matter.

[#15]

@fredc2022 

There may be a misunderstanding here. I thought that you wanted the code to run through all team names. So it does not look at the team name selected in cell B2, but instead processes all team names in I4:I35.

Do you want to process only the team name selected in B2?

[#16]

Hello Hans,
I didn't expect you to post at this time. Thanks for doing so.
Yes, I wanted to go through all the teams by week and copy and paste their drive number to the right of their Team Names (in column "I") to the week column that starts in column ("L".)
What's happening is the the same drive number is being placed in all team names. The code is not picking up each teams drive number.
I ran the code using different team names, New England Patriots, who's first week drives were 8. The 8 was placed in all team names.
So, it would appear that there's no need for a dropdown list in "B2". Just the name "Arizona Cardinals" and the code would run down the names in column "I". Is that correct?

[#17]

Good morning Hans,
As I see it the "Drives" will need to be coded the same as the "Team Name", where as the column number will need to be used so the team and drives match.

The "DriveWorkSheet" data currently is for the whole year. When the season is playing out the data will come in one week at a time. That is why the code needs to run on a weekly basis.

I hope that I have clarified what is needed.

[#18 · Accepted solution]

@fredc2022 

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.

[#19]

Hans,
I don't know how to properly thank you for your time and knowledge. You have done a great job in answering and solving my problem. Thank you! Thank you!'
I'll continue reading your posts, just to learn a small amount of your knowledge. You are a great service to this site.

[#20]

@fredc2022 

You're welcome!