VBA EXCEL Multiple Nested FOR Loops that Set two variable for expression

Question

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

Gaffi · Accepted Answer · 2012-03-14 17:41:04Z

3

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

edited Mar 14, 2012 at 17:41

answered Mar 13, 2012 at 19:43

Gaffi

4,3758 gold badges47 silver badges73 bronze badges

Sign up to request clarification or add additional context in comments.

8 Comments

PCGIZMO Over a year ago

Thank you for the Reply. This helps me see how the conditional exit works with my idea and maybe that my approach on this is incorrect. With this and the codes that I have fuddled with before I cannot get the loops to sync. I’ll use column D as an example In column d I have 1 cell for output that will =t 1 cell for y with a time X consists of 4 cells d8,d9,d10,d11 that will have a only one value for the loop in column d.

PCGIZMO Over a year ago

In essence I need to… set corresponding y value In column d , search through the 4 cells for the value of x, calculate T. Once T is calculated I need to pick the whole thing up move it to the next column currently columns D through CV. Am I on the right approach? I’m thinking that with this method and x scanning through column then row instead of row then next column is also going to be an issue. I have not done much programming well since about 98,99 and that was in qbasic. So I’m winging

Gaffi Over a year ago

@PCGIZMO Ah, I started with QB way back when. :-) Can you post in your question an example of your data and the output you expect to receive from it? I think this will help me as I'm still not 100% sure I know what you are trying to get at. Are you trying to set the value of the d-cells to be all the same (the value of y)?

PCGIZMO Over a year ago

I will see what i can do i don't have the rep to add photos/screen captures. The Google doc link has the segment of the work book this for this code .

PCGIZMO Over a year ago

COOOOL!! I see where I misled you on Y being static. But thanks to your notes I was able to remedy that :}. I really appreciate your time and patience. I have learned a little more today ... I updated the code in my question it is working as planned ... I never even thought of comparing the columns

|

Collectives™ on Stack Overflow

VBA EXCEL Multiple Nested FOR Loops that Set two variable for expression

1 Answer 1

8 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

8 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related