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

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

This question is related to vba loops excel for-loop nested

The answer is


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

Examples related to vba

Copy filtered data to another sheet using VBA Better way to find last used row Check if a value is in an array or not with Excel VBA Creating an Array from a Range in VBA Excel: macro to export worksheet as CSV file without leaving my current Excel sheet VBA: Convert Text to Number What's the difference between "end" and "exit sub" in VBA? Rename Excel Sheet with VBA Macro Extract Data from PDF and Add to Worksheet Quicker way to get all unique values of a column in VBA?

Examples related to loops

How to increment a letter N times per iteration and store in an array? Angular 2 Cannot find control with unspecified name attribute on formArrays What is the difference between i = i + 1 and i += 1 in a 'for' loop? Prime numbers between 1 to 100 in C Programming Language Python Loop: List Index Out of Range JavaScript: Difference between .forEach() and .map() Why does using from __future__ import print_function breaks Python2-style print? Creating an array from a text file in Bash Iterate through dictionary values? C# Wait until condition is true

Examples related to excel

Python: Pandas pd.read_excel giving ImportError: Install xlrd >= 0.9.0 for Excel support Converting unix time into date-time via excel How to increment a letter N times per iteration and store in an array? 'Microsoft.ACE.OLEDB.16.0' provider is not registered on the local machine. (System.Data) How to import an Excel file into SQL Server? Copy filtered data to another sheet using VBA Better way to find last used row Could pandas use column as index? Check if a value is in an array or not with Excel VBA How to sort dates from Oldest to Newest in Excel?

Examples related to for-loop

List append() in for loop Prime numbers between 1 to 100 in C Programming Language Get current index from foreach loop how to loop through each row of dataFrame in pyspark TypeScript for ... of with index / key? Is there a way in Pandas to use previous row value in dataframe.apply when previous value is also calculated in the apply? Python for and if on one line R for loop skip to next iteration ifelse How to append rows in a pandas dataframe in a for loop? What is the difference between ( for... in ) and ( for... of ) statements?

Examples related to nested

Nested routes with react router v4 / v5 Extract first item of each sublist python "TypeError: 'numpy.float64' object cannot be interpreted as an integer" How can I combine multiple nested Substitute functions in Excel? Retrieving values from nested JSON Object MySQL Nested Select Query? List comprehension on a nested list? Nested ifelse statement Single Line Nested For Loops Nested or Inner Class in PHP