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

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

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.

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

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.

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

Nested For statements
 

noodnutt"AT"gmail"DOT"com