excel VBA If陈述式未正确筛选数据

bkhjykvo 于 2022-12-05 发布在其他

关注(0)|答案(1)|浏览(123)

我目前正在VBA中开发一个自动化报表，该报表会遍历物料列表，并提取出成品中低于特定库存阈值的任何组件。我遇到的问题是，我需要脚本将物料从一个工作表提取到另一个工作表，但我需要它忽略空值或报废的字段。我当前执行这部分报表的代码如下所示：

Do While l <= alarm.Worksheets("Alarms").Cells.CurrentRegion.Rows.Count
    i = 2
    j = 2
    Do While alarm.Worksheets("Alarms").Range("A" & l).Text <> alarm.Worksheets("BOMs").Range("A" & i).Text
        i = i + 1
    Loop
    Do While alarm.Worksheets("Alarms").Range("A" & l).Text = alarm.Worksheets("BOMs").Range("A" & i).Text
        If alarm.Worksheets("BOMs").Range("K" & i).Value / alarm.Worksheets("BOMs").Range("G" & i).Value < 20 Then
            If alarm.Worksheets("BOMs").Range("D" & i).Text <> "SCRAP" Or alarm.Worksheets("BOMs").Range("D" & i).Text <> "" Then
                alarm.Worksheets("Alarms").Cells(l, j) = alarm.Worksheets("BOMs").Cells(i, 4)
                j = j + 1
                i = i + 1
            End If
        Else
            i = i + 1
        End If
    Loop
    l = l + 1
Loop

除了尝试筛选出空和报废组件外，此操作按预期工作。当报表运行时，我仍然看到大量的空和报废字段。我不明白为什么会这样，如果能提供一些指导，将非常感谢！

excel

来源：https://stackoverflow.com/questions/74643967/vba-if-statement-not-filtering-data-properly

1条答案

按热度按时间

svmlkihl1#

巢状Do...循环中的巢状If陈述式

Sub Test()

    Dim alarm As Workbook: Set alarm = ThisWorkbook
    
    Dim aws As Worksheet: Set aws = alarm.Sheets("Alarms")
    Dim alRow As Long: alRow = aws.Range("A1").CurrentRegion.Rows.Count
    
    Dim bws As Worksheet: Set bws = alarm.Sheets("BOMs")
    Dim blRow As Long: blRow = bws.Range("A1").CurrentRegion.Rows.Count

    Dim ar As Long, ac As Long, br As Long
    Dim alString As String, blString As String, bvString As String
    
    ar = 2 ' ?
    Do While ar <= alRow
        alString = aws.Cells(ar, "A").Text
        ac = 2
        br = 2
        Do While br <= blRow
            blString = bws.Cells(br, "A").Text
            If StrComp(alString, blString, vbTextCompare) = 0 Then
                bvString = bws.Cells(br, "D").Text
                If Len(bvString) > 0 Then
                    If StrComp(bvString, "SCRAP", vbTextCompare) <> 0 Then
                        If bws.Cells(br, "K").Value / bws.Cells(br, "G").Value _
                                < 20 Then
                            aws.Cells(ar, ac) = bvString
                            ac = ac + 1
                        End If
                    End If
                End If
            End If
            br = br + 1
        Loop
        ar = ar + 1
    Loop

End Sub

赞(0）回复(0）举报 2022-12-05

我来回答

excel VBA If陈述式未正确筛选数据

1条答案

巢状Do...循环中的巢状If陈述式

相关问题

热门标签

最新问答