Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Nested For statements
Hi Team
I sincerely trust you're all keeping well during this Topsy-turfy time. This is my current code which works as expected: Dim cRoute As String Dim i As Integer For i = 2 To 500 cRoute = Sheets("Conversion").Range("A" & i).Value If Module3.testRouteSheetorAdd(cRoute) Then Call Module3.transferCommittedRoute(cRoute, i) End If Next i I would like to expand it so that it only creates sheets based on the value of another sheet (cStatus = "Y"): This next code is not working: Dim cRoute As String Dim fRoute As String Dim i As Integer Dim j As Integer For i = 5 To 50 cStatus = Sheets("Fleet Summary").Range("H" & i).Value If cStatus = "Y" Then fRoute = Sheets("Fleet Summary").Range("A" & i).Value For j = 2 To 500 cRoute = Sheets("Conversion").Range("A" & j).Value If cRoute = fRoute Then If Module3.testRouteSheetorAdd(cRoute) Then Call Module3.transferCommittedRoute(cRoute, j) End If End If Next j End If Next i As always, many thanks for any assistance. Cheers Mark |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Nested For statements
FWIW - some coding 'best practices':
Note that row/col index Type is Long, NOT integer, even though your working counter is within Int values Note also that the string vars have "s" prefixes and route IDs are appended to the var name. The following is working in a mockup wb: Option Explicit Sub test() Dim sRouteF$, sRouteC$, n&, k& With Sheets("FleetSummary") For n = 5 To 10 If .Range("H" & n) = "Y" Then sRouteF = .Range("A" & n) For k = 2 To 10 sRouteC = Sheets("Conversion").Range("A" & k) If sRouteF = sRouteC Then If testRouteSheet0rAdd(sRouteC) Then _ Call transferCommittedRoute(sRouteC, k) End If 'sRouteF = sRouteC Next 'k End If '.Range("H" & n) = "Y" Next 'n End With 'Sheets("FleetSummary") End Sub Function testRouteSheet0rAdd(sRoute$) As Boolean testRouteSheet0rAdd = MsgBox("Yes or No", vbYesNo) = vbYes End Function Sub transferCommittedRoute(sRoute$, Ndx&) MsgBox "Transferred " & sRoute & ", " & Ndx, vbExclamation End Sub -- Garry Free usenet access at http://www.eternal-september.org Classic VB Users Regroup! comp.lang.basic.visual.misc microsoft.public.vb.general.discussion |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Nested For statements
Hi Garry
There is definitely added weight to my wife's needling in that I am guilty of assuming people just understand instantly what I am banging on about, for this I must apologise. Let me restart by stating that the existing codes work really well, but it creates all route sheets; I want the user to have the option to exclude any route. It's my fault your code did not work as I failed to include the module3 codes that do the important stuff which would have given you a clearer insight in understanding what they do to generate these route sheets. I was hoping to have the user look thru the "Fleet Summary" sheet first, selecting the routes he/she wants to actually include in the route sheet creation phase as each day will be different; most, if not all routes will be included in the EDI (Download File), but one or two may not be able to be covered due to driver absenteeism or vehicle mechanical issues. Function testRouteSheetorAdd(tSheet As String) As Boolean found = False For Each wksheet In ActiveWorkbook.Sheets If wksheet.Name = tSheet Then testRouteSheetorAdd = True found = True Exit For End If Next wksheet If found = False Then Sheets("TEMPLATE").Copy after:=Sheets(Sheets.Count) actSheet = ActiveSheet.Name Sheets(actSheet).Name = tSheet Range("A1:B3").Select ActiveCell.FormulaR1C1 = tSheet testRouteSheetorAdd = True End If End Function Sub TansferCommittedRoute(nSheet As String, rw As Integer) Sheets("Conversion").Select tLoad = Sheets("Conversion").Range("A" & rw).Value For j = 8 To 69 cLoad = Sheets(nSheet).Range("A" & j).Value If cLoad = "" Then If Application.WorksheetFunction.CountA(Worksheets(nS heet).Range("A" & j & ":A" & j + 50)) = 0 Then 'Transfer the Route Ref Worksheets(nSheet).Range("A" & j).Value = Format(Sheets("Conversion").Range("B" & rw).Value, "00") 'Shipment Custom Worksheets(nSheet).Range("B" & j).Value = Sheets("Conversion").Range("C" & rw).Value 'Customer Name Worksheets(nSheet).Range("C" & j).Value = Sheets("Conversion").Range("D" & rw).Value 'Address Worksheets(nSheet).Range("D" & j).Value = Sheets("Conversion").Range("E" & rw).Value 'Suburb/City Worksheets(nSheet).Range("E" & j).Value = Sheets("Conversion").Range("F" & rw).Value 'Arrival Time Worksheets(nSheet).Range("F" & j).Value = Sheets("Conversion").Range("G" & rw).Value 'Time on Site Worksheets(nSheet).Range("G" & j).Value = Sheets("Conversion").Range("H" & rw).Value 'Departure Time Worksheets(nSheet).Range("H" & j).Value = Sheets("Conversion").Range("I" & rw).Value qty = Sheets("Conversion").Range("J" & rw).Value Worksheets(nSheet).Range("I" & j).Value = qty 'Transfer WEight wght = Sheets("Conversion").Range("K" & rw).Value Worksheets(nSheet).Range("J" & j).Value = wght 'Special Worksheets(nSheet).Range("K" & j).Value = Sheets("Conversion").Range("M" & rw).Value 'Transfer the Route Ref Worksheets(nSheet).Range("A" & j + 1).Value = Format(Sheets("Conversion").Range("B" & rw).Value, "00") 'Time Window Label Worksheets(nSheet).Range("B" & j + 1).Value = "Time Window:" Worksheets(nSheet).Range("B" & j + 1).Font.Bold = True 'Time Window Instructions Worksheets(nSheet).Range("C" & j + 1).Value = Sheets("Conversion").Range("L" & rw).Value 'Customer Note Label Worksheets(nSheet).Range("E" & j + 1).Value = "Customer Notes:" Worksheets(nSheet).Range("E" & j + 1).Font.Bold = True 'Customer Note Instructions Worksheets(nSheet).Range("F" & j + 1).Value = Sheets("Conversion").Range("N" & rw).Value Exit For End If End If If tLoad = cLoad Then MsgBox ("Load already exists, error?") End If Next j sortFunc = "A" 'Sort the new sheet so that ref's are grouped Sheets(nSheet).Select Range("A8:K" & j).Select Selection.Sort Key1:=Range(sortFunc & "8"), Order1:=xlAscending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal Range("A8:K" & j + 1).Select Selection.Sort Key1:=Range(sortFunc & "8"), Order1:=xlAscending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal 'add values to Fleet Summary Sheet For i = 5 To 50 If Sheets("FLEET SUMMARY").Range("A" & i).Value = nSheet Then Sheets("FLEET SUMMARY").Range("C" & i).Value = Sheets("FLEET SUMMARY").Range("C" & i).Value + qty Sheets("FLEET SUMMARY").Range("D" & i).Value = Sheets("FLEET SUMMARY").Range("D" & i).Value + wght Exit For End If Next i End Sub These codes were in an existing application created by another programmer (no longer with the company) many, many years ago for a contract; I have been using and modifying it ever since; And even though the code is doing exactly the same thing for this new project, the data and structure is different, which is why I would like the user to have some flexibility with it. Thank you again Cheers Mark. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Nested For statements
Let me restart by stating that the existing codes work really well, but it
creates all route sheets; I want the user to have the option to exclude any route. It's my fault your code did not work as I failed to include the module3 codes that do the important stuff which would have given you a clearer insight in understanding what they do to generate these route sheets. My sample code for your 2 routines was for testing purposes only; - I didn't need actual code for that purpose. Sounds like you are trying to bandaid the legacy project when what's really needed is a redesign that will more readily accommodate changes going forward. To help with that I'll need a copy of the project along with a complete explanation of its intent. (You do know there are route/driver solutions out there, right?) And even though the code is doing exactly the same thing for this new project, the data and structure is different, which is why I would like the user to have some flexibility with it. The problem with coding for data is that the data MUST be structured so the code works with it; OR the code must be structured to work with the data as structured. User can not play around with either at whim! That said, without seeing your data structure its impossible to write code to do the task you wnat it to do. Nor is it possible to write code without knowing what it is that you are trying to accomplish in the context of your project's intent. Another thing that concerns me is this code makes up variables as it goes; - *very bad idea* and 100% contrary to accepted best practices! You need to go into ToolsOptions and check the following option on the Editor tab... Require Variable Declaration ...then go to DebugCompile VBA Project to see why! -- Garry Free usenet access at http://www.eternal-september.org Classic VB Users Regroup! comp.lang.basic.visual.misc microsoft.public.vb.general.discussion |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Nested For statements
Garry
Thank you again for your constructive comments and time. Yep! Band aids are all I have at present, hence the reason I rely heavily on the good folk here who give so generously of their knowledge and time. The reason I use band-aids is they work for my purposes, I prefer not to re-invent the wheel each time if the projects are almost identical. And you'd be right in thinking it's dirty from a programmers perspective, and it may not sit right, or be right, but! the view from my cage where I sit, I don't have the luxury of time given i'm just one person, so any shortcuts to get me over the line with a working model ticks my boxes. kinda Reminiscent of Microsoft spewing their rubbish into the market to capture the earliest dollar, then fixing the bugs with patches and updates later. The peeps who run my circus have just wasted 3 years and millions on a failed re-vamped modular database environment to bridge across multiple contracts. Sadly, they're not looking to spend money on pre-packaged routers given we already have one, even if it's not flashy, it's economical until they recoup some lost venture capital. Welcome to my cage, I see and actively look for better, improved applications, frustratingly, I may as well play in the muck on the floor of my cage as it's way above my pay-grade and they just don't listen, always opting for the cheapest outcome. I'm more than happy to send it to you (it's still a patchwork, but getting there). I am confident that you're professional enough regarding the (less than world shattering) data itself, so i'm comfy sending it inclusive. mail me directly with your email address Thank you again. Cheers Mark. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Nested For statements
Garry
Thank you again for your constructive comments and time. Yep! Band aids are all I have at present, hence the reason I rely heavily on the good folk here who give so generously of their knowledge and time. The reason I use band-aids is they work for my purposes, I prefer not to re-invent the wheel each time if the projects are almost identical. And you'd be right in thinking it's dirty from a programmers perspective, and it may not sit right, or be right, but! the view from my cage where I sit, I don't have the luxury of time given i'm just one person, so any shortcuts to get me over the line with a working model ticks my boxes. kinda Reminiscent of Microsoft spewing their rubbish into the market to capture the earliest dollar, then fixing the bugs with patches and updates later. The peeps who run my circus have just wasted 3 years and millions on a failed re-vamped modular database environment to bridge across multiple contracts. Sadly, they're not looking to spend money on pre-packaged routers given we already have one, even if it's not flashy, it's economical until they recoup some lost venture capital. Welcome to my cage, I see and actively look for better, improved applications, frustratingly, I may as well play in the muck on the floor of my cage as it's way above my pay-grade and they just don't listen, always opting for the cheapest outcome. I'm more than happy to send it to you (it's still a patchwork, but getting there). I am confident that you're professional enough regarding the (less than world shattering) data itself, so i'm comfy sending it inclusive. mail me directly with your email address Thank you again. Cheers Mark. Ok, I'll have a look at it. I can't mail you directly until you post your email address. -- Garry Free usenet access at http://www.eternal-september.org Classic VB Users Regroup! comp.lang.basic.visual.misc microsoft.public.vb.general.discussion |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Nested For statements
noodnutt"AT"gmail"DOT"com
|
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Nested if statements | Excel Worksheet Functions | |||
Too Many Nested IF Statements! | Excel Worksheet Functions | |||
Nested IF statements | Excel Programming | |||
nested if statements | Excel Worksheet Functions | |||
Nested If statements | Excel Programming |