Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Loop routine fails after 10 cycles......
Please assist
I am working on the below routine. This is a fantasy football application that allows a team owner to check and see what his chosen teams are for each week. The routine works great when an owner chooses which week they would like to look at. I have a button that is labeled "Previous". This routine invokes the below routine and pulls up the correct info for the correct week. There are 17 weeks in the NFL regular season. For the problem....... When an owner clicks the Prev button for weeks 17 down to week 7 the routine works perfectly. When the Prev button is clicked one more time to go the week 6, the routine will bring up week 16 rather than week 6. Any help would be great.... Sub LoadStandings(week As Integer) Dim RecordFound As Range Dim FindRange As Range GetSheet ("Standings") Set FindRange = Worksheets("Standings").Range("A2:A20") Set RecordFound = FindRange.Find(What:=week, LookAt:=xlPart, _ LookIn:=xlValues, SearchOrder:=xlByColumns) If Not RecordFound Is Nothing Then RecordFound.Activate Dashboard.T1Name.Value = Cells(ActiveCell.Row, 2) Dashboard.T1W.Value = Cells(ActiveCell.Row, 3) Dashboard.T1L.Value = Cells(ActiveCell.Row, 4) Dashboard.T1T.Value = Cells(ActiveCell.Row, 5) Dashboard.T2Name.Value = Cells(ActiveCell.Row, 6) Dashboard.T2W.Value = Cells(ActiveCell.Row, 7) Dashboard.T2L.Value = Cells(ActiveCell.Row, 8) Dashboard.T2T.Value = Cells(ActiveCell.Row, 9) Dashboard.T3Name.Value = Cells(ActiveCell.Row, 10) Dashboard.T3W.Value = Cells(ActiveCell.Row, 11) Dashboard.T3L.Value = Cells(ActiveCell.Row, 12) Dashboard.T3T.Value = Cells(ActiveCell.Row, 13) Dashboard.T4Name.Value = Cells(ActiveCell.Row, 14) Dashboard.T4W.Value = Cells(ActiveCell.Row, 15) Dashboard.T4L.Value = Cells(ActiveCell.Row, 16) Dashboard.T4T.Value = Cells(ActiveCell.Row, 17) Dashboard.T5Name.Value = Cells(ActiveCell.Row, 18) Dashboard.T5W.Value = Cells(ActiveCell.Row, 19) Dashboard.T5L.Value = Cells(ActiveCell.Row, 20) Dashboard.T5T.Value = Cells(ActiveCell.Row, 21) Dashboard.T6Name.Value = Cells(ActiveCell.Row, 22) Dashboard.T6W.Value = Cells(ActiveCell.Row, 23) Dashboard.T6L.Value = Cells(ActiveCell.Row, 24) Dashboard.T6T.Value = Cells(ActiveCell.Row, 25) End If End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Loop routine fails after 10 cycles......
How is loadstandings called? Maybe you're passing the wrong week???
And did you really mean to use xlpart in the .find command? Tom wrote: Please assist I am working on the below routine. This is a fantasy football application that allows a team owner to check and see what his chosen teams are for each week. The routine works great when an owner chooses which week they would like to look at. I have a button that is labeled "Previous". This routine invokes the below routine and pulls up the correct info for the correct week. There are 17 weeks in the NFL regular season. For the problem....... When an owner clicks the Prev button for weeks 17 down to week 7 the routine works perfectly. When the Prev button is clicked one more time to go the week 6, the routine will bring up week 16 rather than week 6. Any help would be great.... Sub LoadStandings(week As Integer) Dim RecordFound As Range Dim FindRange As Range GetSheet ("Standings") Set FindRange = Worksheets("Standings").Range("A2:A20") Set RecordFound = FindRange.Find(What:=week, LookAt:=xlPart, _ LookIn:=xlValues, SearchOrder:=xlByColumns) If Not RecordFound Is Nothing Then RecordFound.Activate Dashboard.T1Name.Value = Cells(ActiveCell.Row, 2) Dashboard.T1W.Value = Cells(ActiveCell.Row, 3) Dashboard.T1L.Value = Cells(ActiveCell.Row, 4) Dashboard.T1T.Value = Cells(ActiveCell.Row, 5) Dashboard.T2Name.Value = Cells(ActiveCell.Row, 6) Dashboard.T2W.Value = Cells(ActiveCell.Row, 7) Dashboard.T2L.Value = Cells(ActiveCell.Row, 8) Dashboard.T2T.Value = Cells(ActiveCell.Row, 9) Dashboard.T3Name.Value = Cells(ActiveCell.Row, 10) Dashboard.T3W.Value = Cells(ActiveCell.Row, 11) Dashboard.T3L.Value = Cells(ActiveCell.Row, 12) Dashboard.T3T.Value = Cells(ActiveCell.Row, 13) Dashboard.T4Name.Value = Cells(ActiveCell.Row, 14) Dashboard.T4W.Value = Cells(ActiveCell.Row, 15) Dashboard.T4L.Value = Cells(ActiveCell.Row, 16) Dashboard.T4T.Value = Cells(ActiveCell.Row, 17) Dashboard.T5Name.Value = Cells(ActiveCell.Row, 18) Dashboard.T5W.Value = Cells(ActiveCell.Row, 19) Dashboard.T5L.Value = Cells(ActiveCell.Row, 20) Dashboard.T5T.Value = Cells(ActiveCell.Row, 21) Dashboard.T6Name.Value = Cells(ActiveCell.Row, 22) Dashboard.T6W.Value = Cells(ActiveCell.Row, 23) Dashboard.T6L.Value = Cells(ActiveCell.Row, 24) Dashboard.T6T.Value = Cells(ActiveCell.Row, 25) End If End Sub -- Dave Peterson |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Loop routine fails after 10 cycles......
Tom,
I would guess that because you are searching in "xlPart", it finds the "6" in "16" before it finds the single entry "6". Can you not .Look At xlWhole, or have 2 digit weeks, "06" ? NickHK P.S. There's no need to .Activate. You could: With RecordFound Dashboard.T1Name.Value = .Offset(0,2).Value ........ "Tom" wrote in message oups.com... Please assist I am working on the below routine. This is a fantasy football application that allows a team owner to check and see what his chosen teams are for each week. The routine works great when an owner chooses which week they would like to look at. I have a button that is labeled "Previous". This routine invokes the below routine and pulls up the correct info for the correct week. There are 17 weeks in the NFL regular season. For the problem....... When an owner clicks the Prev button for weeks 17 down to week 7 the routine works perfectly. When the Prev button is clicked one more time to go the week 6, the routine will bring up week 16 rather than week 6. Any help would be great.... Sub LoadStandings(week As Integer) Dim RecordFound As Range Dim FindRange As Range GetSheet ("Standings") Set FindRange = Worksheets("Standings").Range("A2:A20") Set RecordFound = FindRange.Find(What:=week, LookAt:=xlPart, _ LookIn:=xlValues, SearchOrder:=xlByColumns) If Not RecordFound Is Nothing Then RecordFound.Activate Dashboard.T1Name.Value = Cells(ActiveCell.Row, 2) Dashboard.T1W.Value = Cells(ActiveCell.Row, 3) Dashboard.T1L.Value = Cells(ActiveCell.Row, 4) Dashboard.T1T.Value = Cells(ActiveCell.Row, 5) Dashboard.T2Name.Value = Cells(ActiveCell.Row, 6) Dashboard.T2W.Value = Cells(ActiveCell.Row, 7) Dashboard.T2L.Value = Cells(ActiveCell.Row, 8) Dashboard.T2T.Value = Cells(ActiveCell.Row, 9) Dashboard.T3Name.Value = Cells(ActiveCell.Row, 10) Dashboard.T3W.Value = Cells(ActiveCell.Row, 11) Dashboard.T3L.Value = Cells(ActiveCell.Row, 12) Dashboard.T3T.Value = Cells(ActiveCell.Row, 13) Dashboard.T4Name.Value = Cells(ActiveCell.Row, 14) Dashboard.T4W.Value = Cells(ActiveCell.Row, 15) Dashboard.T4L.Value = Cells(ActiveCell.Row, 16) Dashboard.T4T.Value = Cells(ActiveCell.Row, 17) Dashboard.T5Name.Value = Cells(ActiveCell.Row, 18) Dashboard.T5W.Value = Cells(ActiveCell.Row, 19) Dashboard.T5L.Value = Cells(ActiveCell.Row, 20) Dashboard.T5T.Value = Cells(ActiveCell.Row, 21) Dashboard.T6Name.Value = Cells(ActiveCell.Row, 22) Dashboard.T6W.Value = Cells(ActiveCell.Row, 23) Dashboard.T6L.Value = Cells(ActiveCell.Row, 24) Dashboard.T6T.Value = Cells(ActiveCell.Row, 25) End If End Sub |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Loop routine fails after 10 cycles......
that worked.....thx
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Loop routine fails after 10 cycles......
that worked great. thx
|
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do you change seconds to cycles? | Excel Worksheet Functions | |||
need error loop in pivot table routine | Excel Discussion (Misc queries) | |||
Excell freezez after many macro cycles. | Excel Discussion (Misc queries) | |||
Advancing outer Loop Based on criteria of inner loop | Excel Programming | |||
[HELP] How to use command buttons in cycles | Excel Programming |