excel 带有两个条件的For语句

92dk7w1h 于 2023-10-21 发布在其他

关注(0)|答案(2)|浏览(117)

我试着把所有的代码合并组合在一起，让它一次看一张唱片。代码的第二部分是查看循环中的所有行。理想情况下，我希望查看客户A的记录并隐藏行#66或行#64。在此之后，代码应该从顶部重新开始，查看客户B，C，D.感谢回答我关于for loop问题的人。谢谢你

Dim c As Range
Dim d As Range
With ThisWorkbook.Sheets("Template")
    .Rows.EntireRow.Hidden = False
    .Activate
    For Each c In .Range("E27,E32,E35,L36,L37,E39,E40,E41,E42,E43,E44,E45,E46,E49,E50,E51")
        If c.Value = 0 Then
            c.EntireRow.Hidden = True
        Else
            c.EntireRow.Hidden = False
        End If
    Next
    
   
End With


With Worksheets("Template")
    If .Range("E26").Value = 0 Or .Range("E27") = 0 Then
    Worksheets("Template").Rows("69").EntireRow.Hidden = True
    Else
    Worksheets("Template").Rows("69").EntireRow.Hidden = False
    End If
    If .Range("E32").Value = 0 Then
    Worksheets("Template").Rows("70").EntireRow.Hidden = True
    Else
    Worksheets("Template").Rows("70").EntireRow.Hidden = False
    End If

End With

Dim r As Range
Dim ws2 As Worksheet: Set ws2 = ThisWorkbook.Worksheets("Data File")
    For Each r In ws2.Range("CU3:CU5")
    If r.Value = "RSU" And r.Offset(0, 1).Value = "Y" Then
        Worksheets("Template").Rows("66").EntireRow.Hidden = False
        Worksheets("Template").Rows("64").EntireRow.Hidden = True
    Else
        Worksheets("Template").Rows("66").EntireRow.Hidden = True
        Worksheets("Template").Rows("64").EntireRow.Hidden = False
    End If
Next r

excel

来源：https://stackoverflow.com/questions/77319922/for-statement-with-two-conditions

2条答案

按热度按时间

pw136qt21#

这里有一种方法，但我仍然很困惑，你如何解释你循环的范围中的多行-你将得到从该范围的最后一行开始的任何匹配结果。

Dim rw As Range, matched As Boolean
For Each rw In ws2.Range("CU3:CV5").Rows  'loop over rows in range
    'row matches conditions?
    matched = rw.Cells(1).Value = "RSU" And rw.Cells(2).Value = "Y"
    With Worksheets("Template")
        .Rows("66").EntireRow.Hidden = Not matched
        .Rows("64").EntireRow.Hidden = matched
    End With
Next rw

编辑：更新后

Dim c As Range, ws As Range

Set ws = ThisWorkbook.Worksheets("Template")

ws.Rows.EntireRow.Hidden = False
For Each c In ws.Range("E27,E32,E35,L36,L37,E39,E40,E41,E42,E43,E44,E45,E46,E49,E50,E51").Cells
    If c.Value = 0 Then c.EntireRow.Hidden = c.Value = 0
Next
    
ws.Rows(69).EntireRow.Hidden = ws.Range("E26").Value = 0 Or ws.Range("E27") = 0
ws.Rows(70).EntireRow.Hidden = ws.Range("E32").Value = 0

赞(0）回复(0）举报 2023-10-21

hof1towb2#

这就是你要找的。和@Tim的评论一样。您不必在For子句中保留Two Conditions。

Dim r As Range
For Each r In ws2.Range("CU3:CU5")
    If r.Value = "RSU" And r.Offset(0, 1).Value = "Y" Then
        Worksheets("Template").Rows("66").EntireRow.Hidden = False
        Worksheets("Template").Rows("64").EntireRow.Hidden = True
    Else
        Worksheets("Template").Rows("66").EntireRow.Hidden = True
        Worksheets("Template").Rows("64").EntireRow.Hidden = False
    End If
Next r

代码可以简化如下。

Dim r As Range, bFlag As Boolean
    For Each r In ws2.Range("CU3:CU5")
        bFlag = (r.Value = "RSU" And r.Offset(0, 1).Value = "Y")
        With Worksheets("Template")
            .Rows(66).EntireRow.Hidden = Not bFlag
            .Rows(64).EntireRow.Hidden = bFlag
        End With
    Next r

赞(0）回复(0）举报 2023-10-21

我来回答

excel 带有两个条件的For语句

2条答案

相关问题

热门标签

最新问答