jeudi 15 août 2019

Cannot use an If-statement with multiple conditions (AND/OR) when looping through variant array?

Background:

In working with some variant arrays to sort data to multiple locations based on criteria, I noticed that using an if-statement with multiple criteria flagged as false every time. This was being used to create a dictionary, though it never made it to the dictionary aspect, due to the false response when looping only through the variant array.

I moved these to two separate if-statements and everything worked as expected.


Question:

Why am I unable to use a multi-condition if-statement when looping through data in a variant array?


Code in question:

General code to generated the variant array:

Public ex_arr As Variant, ex_lr As Long, ex_lc As Long
Public dc As Scripting.Dictionary 

Private Sub capture_export_array()
    With Sheets("export")
        ex_lc = .Cells(1, .Columns.Count).End(xlToLeft).Column
        ex_lr = .Cells(.Rows.Count, ex_lc).End(xlUp).Row
        ex_arr = .Range(.Cells(1, 1), .Cells(ex_lr, ex_lc)).Value
    End With
End Sub

Code that resulted in False conditions (immediate window print = 0):

Private Sub find_unique_items()
    Set dc = New Scripting.Dictionary
    Dim i As Long
    For i = LBound(ex_arr) To UBound(ex_arr)
        If InStr(ex_arr(i, ex_lc), "CriteriaA") And InStr(ex_arr(i, 4), "CriteriaB") Then dc(ex_arr(i, 2)) = ex_arr(i, 3)
    Next i
    Debug.Print dc.Count
End Sub

Code that resulted in desired output (immediate window print > 0):

Private Sub find_unique_items()
    Set dc = New Scripting.Dictionary
    Dim i As Long
    For i = LBound(ex_arr) To UBound(ex_arr)
        If InStr(ex_arr(i, ex_lc), "CriteriaA") Then
            If InStr(ex_arr(i, 4), "CriteriaB") Then dc(ex_arr(i, 2)) = ex_arr(i, 3)
        End If
    Next i
    Debug.Print dc.Count
End Sub

Aucun commentaire:

Enregistrer un commentaire