VBA EXCEL Multiple Nested FOR Loops that Set two variable for expression
I can't get to your google docs file at the moment but there are some issues with your code that I will try to address while answering
Sub stituterangersNEW()
Dim t As Range
Dim x As Range
Dim dify As Boolean
Dim difx As Boolean
Dim time2 As Date
Dim time1 As Date
'You said time1 doesn't change, so I left it in a singe cell.
'If that is not correct, you will have to play with this some more.
time1 = Range("A6").Value
'Looping through each of our output cells.
For Each t In Range("B7:E9") 'Change these to match your real ranges.
'Looping through each departure date/time.
'(Only one row in your example. This can be adjusted if needed.)
For Each x In Range("B2:E2") 'Change these to match your real ranges.
'Check to see if our dep time corresponds to
'the matching column in our output
If t.Column = x.Column Then
'If it does, then check to see what our time value is
If x > 0 Then
time2 = x.Value
'Apply the change to the output cell.
t.Value = time1 - time2
'Exit out of this loop and move to the next output cell.
Exit For
End If
End If
'If the columns don't match, or the x value is not a time
'then we'll move to the next dep time (x)
Next x
Next t
End Sub
EDIT
I changed you worksheet to play with (see above for the new Sub). This probably does not suite your needs directly, but hopefully it will demonstrate the conept behind what I think you want to do. Please keep in mind that this code does not follow all the coding best preactices I would recommend (e.g. validating the time is actually a TIME and not some random other data type).
A B C D E
1 LOAD_NUMBER 1 2 3 4
2 DEPARTURE_TIME_DATE 11/12/2011 19:30 11/12/2011 19:30 11/12/2011 19:30 11/12/2011 20:00
4 Dry_Refrig 7585.1 0 10099.8 16700
6 1/4/2012 19:30
Using the sub I got this output:
A B C D E
7 Friday 1272:00:00 1272:00:00 1272:00:00 1271:30:00
8 Saturday 1272:00:00 1272:00:00 1272:00:00 1271:30:00
9 Thursday 1272:00:00 1272:00:00 1272:00:00 1271:30:00
PCGIZMO
Updated on March 15, 2020Comments
-
PCGIZMO about 4 years
Ok so I’ve done a good deal of searching found some and played and little. I cannot seem to get these loops to work fully I can get on part or another but not the whole. As is the first loop works fine then it goes wonky.
T
is the destination for the expression outputt.Value = time1 - time2
Y
is a set time and date that does not change= time1
X
is time and date and has to be extracted from the range in the same column as the correspondingy
.x= time 2
I have uploaded the corresponding segment of my workbook
https://docs.google.com/open?id=0BzGnV1BGYQbvMERWU3VkdGFTQS1tYXpXcU1Mc3lmUQ
I have played with conditional exits rearranging the for loops. I even considered trying goto until I noticed the large pile of bodies created by its very mention.
I am open to and grateful for any advice or direction. I noticed a few languages have exit and continue options but it does not appear VB does?
Here is the loop I have I have stripped out the mess I made while trying to get it to work.
Sub stituterangers() Dim dify As Boolean Dim difx As Boolean Dim time2 As Date Dim time1 As Date For Each t In range("d7:cv7") For Each x In range("d8:cv11") If x > 0 Then time2 = x For Each y In range("d2:cv2") time1 = y t.Value = time1 - time2 t = 0 Next y Next x Next t End Sub Sub stituterangersNEW() Dim t As range Dim x As range Dim dify As Boolean Dim difx As Boolean Dim time2 As Date Dim time1 As Date On Error Resume Next 'Looping through each of our output cells. For Each t In range("d7:cv7") For Each y In range("d2:cv2") If t.Column = y.Column Then time1 = y.Value If y = 0 Then Exit Sub End If For Each x In range("d8:cv11") 'Check to see if our dep time corresponds to 'the matching column in our output If t.Column = x.Column Then If x > 0 Then time2 = x.Value t.Value = time1 - time2 Exit For End If End If Next x Next y Next t End Sub