I'm currently working on an automated report in VBA that runs through a list of materials and pulls out any components of a finished good that are below a certain stock threshold. The issue I am running into is that I need my script to pull materials from one sheet to another, but I need it to ignore and fields that are null or scrap. My current code to execute this part of the report looks like this:

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

This works as intended with the exception of trying to filter out the null and scrap components. When the report runs I am still flooded with null and scrap fields. I don't understand why this is so some guidance would be greatly appreciated!

CodePudding user response：

Nested If Statements in Nested Do...Loops

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