为表达式设置两个变量的 VBA EXCEL 多个嵌套 FOR 循环

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

提问by PCGIZMO

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.

好吧，我已经做了大量的搜索，找到了一些，玩了一点。我似乎无法让这些循环完全工作，我可以部分完成但不是全部。由于第一个循环工作正常，然后它变得不稳定。

Tis the destination for the expression output t.Value = time1 - time2
Yis a set time and date that does not change = time1
Xis time and date and has to be extracted from the range in the same column as the corresponding y. x= time 2

T是表达式输出的目标t.Value = time1 - time2
Y是设置的时间和日期，不会更改= time1
X是时间和日期，并且必须从与相应的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.

我玩过重新排列 for 循环的条件退出。我什至考虑过尝试 goto，直到我注意到它的提及创建了一大堆尸体。

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?

我对任何建议或方向持开放态度并感激不尽。我注意到一些语言有退出和继续选项，但它没有出现 VB 吗？

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

采纳答案by Gaffi

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

我目前无法访问您的 google 文档文件，但您的代码存在一些问题，我将在回答时尝试解决

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).

我改变了你的工作表来玩（见上面的新子）。这可能并不直接满足您的需求，但希望它能够展示我认为您想要做的事情背后的概念。请记住，这段代码没有遵循我推荐的所有编码最佳实践（例如，验证时间实际上是一个 TIME 而不是一些随机的其他数据类型）。

     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:

使用 sub 我得到了这个输出：

    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