VBA EXCEL多嵌套的FOR循环为表达式设置两个变量 [英] VBA EXCEL Multiple Nested FOR Loops that Set two variable for expression
问题描述
code> T 是表达式输出的目的地
t.Value = time1 - time2 Y 是一个不改变的设定时间和日期 = time1 X 是时间和日期，必须从相应的 x = time 2 我已经上传了我的工作簿的相应部分
https://docs.google.com/open?id=0BzGnV1BGYQbvMERWU3VkdGFTQS1tYXpXcU1Mc3lmUQ
我使用条件退出重新排列for循环。我甚至考虑过试试goto，直到我注意到它所提到的大量的尸体。
我乐于向任何建议或方向表示感谢。我注意到几种语言有退出和继续选项，但它不会出现VB呢？
这里是循环我已经剥离了我试图让它工作的混乱。
<
Dim dify As
Dim difx As
Dim time2 As Date
Dim time1 As Date
对于每个t在范围内（d7：cv7）
对于每个x在范围内（d8：cv11）
如果x> 0 then time2 = x
For Each y In range（d2：cv2）
time1 = y
t.Value = time1 - time2
t = 0
y
下一个x
下一个t
结束小组
$ b小组排序NEW（）
小数t作为范围
Dim x As范围
Dim dify作为布尔
昏暗difx作为布尔
昏暗的时间2作为日期
昏暗的时间1作为日期
在错误恢复下一个
'循环遍历每个输出单元。
对于每个t在范围内（d7：cv7）
对于每个y在范围内（d2：cv2）
如果t .Column = y.Column Then
time1 = y.Value
If y = 0 Then Exit Sub
End If
For Each x In range（d8： cv11）
检查我们的dep时间是否对应于
'在我们输出中的匹配列
如果t.Column = x.Column Then
If x> 0然后
time2 = x.Value
t.Value = time1 - time2
退出
结束如果
结束如果
下一个x
下一个y
下一个t
结束Sub
我目前无法访问您的Google文档，但是您的代码存在一些问题（）
Sub stituterangersNEW（）
Dim t As范围
Dim x As Range
Dim dify As Boolean
Dim difx As Boolean
Dim time2 As Date
Dim time1 As Date
$ b $'你说time1不会改变，所以我把它放在一个单细胞里。
'如果这是不正确的，你将不得不玩这个更多。
time1 =范围（A6）。值
'循环遍历每个输出单元。
For Each t In Range（B7：E9）'改变这些以符合您的实际范围。
通过每个出发日期/时间循环。
'（在你的例子中只有一行，如果需要可以调整）
对于每个x在范围内（B2：E2）'改变它们以匹配你的实际范围。
检查我们的dep时间是否对应于
'在我们的输出中的匹配列
如果t.Column = x.Column Then
'如果是，则检查看看我们的时间价值是
如果x> 0然后
time2 = x.Value
'将更改应用到输出单元格。
t.Value = time1 - time2
'退出此循环并移至下一个输出单元。
退出对于
结束如果
结束如果
'如果列不匹配，或者x值不是时间
'那么我们将移动到（x）
下一个x
下一个t
结束小组
编辑
我改变了你的工作表，见上面新的Sub）。这可能并不能直接满足你的需求，但希望它能展现出我想要做的事情。请记住，这个代码并不遵循我所推荐的所有编码最好的准则（例如，验证时间实际上是一个时间，而不是一些随机的其他数据类型）。
ABCDE
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
使用sub我得到了这个输出：
$ $ $ $ $ $ $
7星期五1272：00：00 1272：00：00 1272：00：00 1271：30：00
8星期六1272：00：00 1272：00：00 1272：00：00 1271：30：00
9星期四1272：00：00 1272：00：00 1272：00：00 1271：30：00
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
这篇关于VBA EXCEL多嵌套的FOR循环为表达式设置两个变量的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
