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 output t.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 corresponding y
. 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
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
Source: Stackoverflow.com