User Profile
fredc2022
Brass Contributor
Joined Apr 25, 2022
User Widgets
Recent Discussions
Re: VBA Find (What:="") tied to dropdown list box
HansVogelaar I'll try to clarify this as best I can and hope I don't confuse you more. Here is your original code. This code works with two worksheets, 1) DROPDOWNLISTBOX, AND 2) DRIVEWORKSHEET. The DRIVEWORKSHEET holds the data that I want to find. The DROPDOWNLISTBOX has a drop-down list box with weeks 1 thru 22 to pick from. The worksheet also holds in column (I) the Team Names and in column (J) the Drive Names of each team. It also has the original range where the Drive Numbers counted would be copied. That range is (L4:Ag35). Above that range are Week Number headers, Week 1 thru Week 22. The code below accomplished that. I have added a Home (AL4:BG35) and an Away (BK4:CF35) range in the DROPDOWNLISTBOX with the same headers for Week 1 thru Week 22 for both. What I want to do now is the same thing but I want to sort the drives into Home and Away. And to do that we need to look at the structured layout of the data worksheet DRIVEWORKSHEET. The original code does several lookups, 1) finds the Team Name, 2) xldowns I row to find the Week Number, then offsets 3 columns left to find the Drive Names. The Drive Name for the Team Name being looked up could be in either one row down (Home) or 25 rows down (Away). And that is where I need your help. Modify the code so when the Drive Name is found, then determine if it's Home (row 1)or Away (row 25) and populate the drive count number into the proper range. Sub FIND_TEAMNAME_WEEK_DRIVES2() Dim ws As Worksheet Dim wt As Worksheet Dim wu 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 Dim nt As Long Dim nf As Long Dim rc As Range Dim u As Long Dim v As Long Application.ScreenUpdating = False Set ws = Worksheets("DRIVEWORKSHEET") Set wt = Worksheets("DROPDOWNLISTBOX") Set wu = Worksheets("Drive Count") ' Get Call Week Number from DROPDOWNLISTBOX COLUMN D2 wk = wt.Range("D2").Value m = wt.Range("I3").End(xlDown).Row For r = 4 To m ' Get Team Name from DROPDOWNLISTBOX COLUMN I tm = wt.Range("I" & r).Value ' Find Team Name from DRIVEWORKSHEET Set rg = ws.Range("B:D").Find(What:=tm, LookAt:=xlPart, MatchCase:=False) If Not rg Is Nothing Then ' Find Week Number in the DRIVEWORKSHEET Set rg = rg.Offset(1).EntireRow.Find(What:=wk, LookAt:=xlWhole, MatchCase:=False) If Not rg Is Nothing Then ' Find Drive Name from DROPDOWNLISTBOX column J dr = wt.Range("J" & r).Value ' Find Drive Name on 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 values (Count) for Touchdowns, and Field Goals from DRIVEWORKSHEET n = rg.Offset(5).End(xlDown).Value ' Reset touchdown count and field goal count nt = 0 nf = 0 ' Count touchdowns and field goals For Each rc In rg.Offset(6, 7).Resize(n) Select Case rc.Value Case "Touchdown" nt = nt + 1 Case "Field Goal" nf = nf + 1 End Select Next rc ' 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 ' Find team on Drive count Set rg = wu.Range("B:B").Find(What:=tm, LookAt:=xlWhole, MatchCase:=False) If Not rg Is Nothing Then u = rg.Row ' Find week on Drive Count Set rg = wu.Range("3:3").Find(What:=wk, LookAt:=xlWhole, MatchCase:=False) If Not rg Is Nothing Then v = rg.Column ' Fill Drives, Touchdowns,and Field Goals in DRIVE COUNT WORKSHEET wu.Cells(u, v - 1).Value = n If nt > 0 Then wu.Cells(u, v).Value = nt End If If nf > 0 Then wu.Cells(u, v + 1).Value = nf End If End If End If End If End If End If Next r Application.ScreenUpdating = True End Sub1KViews0likes2CommentsRe: VBA Find (What:="") tied to dropdown list box
HANS, I Think this part is what I'm missing and where it need to be modified to determine a 'Home' drive or an 'Away' Drive. " ' Find Week on DROPDOWNLISTBOX Set rg = wt.Range("3:3").Find(What:=wk, LookAt:=xlWhole, MatchCase:=False) If Not rg Is Nothing Then" Then use the "1 row down' for home and 25 down for away. As this finds the copy to in the old code. Sub FIND_TEAMNAME_WEEK_DRIVES_HOME_AND_AWAY_ED6_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") 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 ' Find Week on DROPDOWNLISTBOX Set rg = wt.Range("3:3").Find(What:=wk, LookAt:=xlWhole, MatchCase:=False) If Not rg Is Nothing Then ' 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 Sub1KViews0likes4CommentsRe: VBA Find (What:="") tied to dropdown list box
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.1KViews0likes6CommentsRe: VBA Find (What:="") tied to dropdown list box
I wasn't able to download the link you provided at first but I have now and I see the code works. I then copied the TeamNames to my Drive Count worksheet and ran the code. However, it did not run but did put the counted number of drives in the DROPDOWNLISTBOX. WOULD IT BE POSSIBLE TO HAVE THE CODE PUT THE TEAM NAME IN DRIVE COUNT WORKSHEET FOR EACH WEEK? I checked to make sure the Drive Count was spelled correctly and that I had no space at the beginning or end. I'm not sure why my original is not working with the code. Please tell me if you can, if I delete the Drive Count worksheet and then add that same named sheet back, would that cause the code to have a problem running? Anyway, I thank you for your help in modifying the code. I worked with it but just could get it to work but I knew you could. Your knowledge is fabulous along with your graciousness to help others. Thanks again! Keep up your good work.4.1KViews0likes10CommentsRe: VBA Find (What:="") tied to dropdown list box
HansVogelaar Hello Hans, I wonder if you were able to run this code. I ran it and it only put the drive count number in the 'DROPDOWLISTBOX' in whatever week was called. Nothing was copied to the 'Drive Count' worksheet for any week I ran. I have a question about this statement; ' Find team on Drive count Set rg = wu.Range("B:B").Find(What:=tm, LookAt:=xlWhole, MatchCase:=False) If Not rg Is Nothing Then u = rg.Row There would not be any 'Team Names' in the 'Drive Count' worksheet until the data was filled.4.1KViews0likes12CommentsRe: VBA Find (What:="") tied to dropdown list box
HansVogelaar I apologize for the confusion. I have attached the 'DRIVE-COUNT-WS-WITH-DATA' that contains what the first week of games played would look like. What we had at the begging were two worksheets. 1) DROPDOWMLISTBOX, 2) DRIVEWORKSHEET. In cell 'D2' of the 'DROPDOWNLISTBOX' we have a dropdown list box with 22 weeks to choose from. 1) The macro takes that 'week #' and goes to the 'DRIVEWORKSHEET'. The 'DRIVEWORKSHEET' is set up as follows. the 'TEAM NAMES' are in column 'B:D' with the first 'TEAM NAME' in row 4. The other thirty-one (31) are in alphabetical order and are fifty-two (52) rows apart. For example: The second 'TEAM NAME' would be in 'B56:D56", the third would be in 'B108:D108', and the last would be in 'B1616:D1616'. 2) One row below (row 5) each 'TEAM NAME' has twenty-two (22) weeks from left to right with the first week in the fifth column (cell E5) and each successive week is ten (10) columns right. For example: week 1=E, week 2=O, week 3=Y, week 4=AI. 3) Once the 'TEAM NAME' and 'WEEK #' have been found then the 'DRIVE NAME' is found. The 'DRIVE NAME' will be offset 3 columns left from the 'WEEK #' and either one (1) or twenty-five (25) rows down. The row number is based on if the "TEAM NAME' being found is the home team or away team. 4) Once the 'DRIVE NAME' is found offset down six (6) rows to the first 'DRIVE #', Then drop down to the first blank cell and count back up the the original cell. For example: the 'ARIZONA CARDINALS', 'Week 1', 'DRIVE NAME' is in 'B6'. Drop down six (6) rows to the first drive 'B12', then drop down to the first black cell, 'B23' and count back up. The count is eleven (11) drives. 5) offset seven (7) columns right in the same range as the drive 'I12:I22' and count the 'Touchdowns and Field Goals. 6) Copy the 'TEAM NAME', the 'DRIVE COUNT', 'TOUCHDOWNS, AND FIELD GOALS' to the 'DRIVE COUNT' worksheet under the "HEADERS'. The 'WEEK #' are in row three (3) and the 'HEADERS' are in row four (4). For example: Week 1 is in column 'D', Week 2 is in column 'I', week 3 is in column 'N' The 'HEADERS' are in row four (4) and are in a four (4) column range with a blank column between each set of 'HEADERS'. For example: The first weeks 'HEADERS' are in range 'B4:E4', week 2 is in 'G4:J4', and week 3 is in 'L4:O4'. The date would be copied starting in row 5. I hope that gives you a better understanding. Thank you for taking the time to help me with this problem.4.1KViews0likes14CommentsRe: VBA Find (What:="") tied to dropdown list box
HansVogelaar Hello Hans, Last time you helped I wanted to count the number of drives each team made per week throughout the 22-week season. That information came from the Driveworksheet, but it has data other than the number of drives. It also gives the results of those drives. Particularly, it tells you if the drive ended in a touchdown or a field goal. What I want to do now is again count the number of drives and also count the number of touchdowns and field goals. It appeared to me that capturing that data on another worksheet would be best. That worksheet is named Drive Count. I think that you can read the macro code and see how we get to the Drives. From there to get to the touchdowns and field goals you would offset 7 columns right. And you would be looking at the same row numbers that the drives were counted. For example: if the drives were in the range B12:B22 the touchdowns and field goals would be in I12:I22. I hope that explains what I want to do. I'll try to clarify any questions you have.4.2KViews0likes17CommentsRe: VBA Find (What:="") tied to dropdown list box
Hello Hans, I made a macro to make the Drive Count worksheet and did that just because there was going to be more data to capture. I still run the macro to populate the Dropdownlistbox range I3:ac35 for the weeks. We could take the headers from the Drive Count worksheet and copy them down to row 40 starting in column B in the Dropdownlistbox. That way we just use two worksheets.4.3KViews0likes1Comment- 4.3KViews0likes24Comments
Re: VBA Find (What:="") tied to dropdown list box
Hello Hans, Hope ou are doing well. I would like to review this thread that you helped me with last year, I would like to modify the macro. We were using two worksheets and I would like to go the 3 worksheets. the new worksheet is called 'Drive Count'. The first worksheet, "Dropdownlistbox" used cell 'D2' to call a week # from 1 to 22. Then go to the 'Driveworksheet' to count the number od drives and copy that number back to the 'Dropdownlistbox" worksheet. Now I want to count the number of 'touchdowns' and 'fieldgoals' along with the 'number of drives'. Are you able to use this links to see the worksheets? DROPDOWNLISTBOX WORKSHEET https://imgur.com/KetyDKZ DRIVEWORKSHEET https://imgur.com/bj5QX6i DRIVE COUNT WORKSHEET https://imgur.com/q342Efm Thanks for your consideration.4.2KViews0likes26CommentsRe: VBA Find (What:="") tied to dropdown list box
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.8.2KViews0likes28CommentsRe: VBA Find (What:="") tied to dropdown list box
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.8.3KViews0likes30CommentsRe: Crear formulario para ser completado por los usuarios e imprimirse desde Excel
Create form to be completed by users and printed from Excel Greetings: I need to create a form in Excel that is to be filled in by users, calculated, printed and can be secured so that it is not edited. Can you provide me with a virtual course or where to look for more information.923Views0likes0CommentsRe: VBA Find (What:="") tied to dropdown list box
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?8.3KViews0likes31Comments
Recent Blog Articles
No content to show