why am I getting a mismatch error on this VBA Script for an array?

Question

Fairly new to Arrays (im trying to speed up a currently slow workbook that uses ranges)

lets say I start out with a table like this (Located in Range "A1:B5" on my worksheet)

and im trying to filter it to only intact (this is a simplified version of what im trying to do irl), why am I getting a Type Mismatch and my output array highlighting?

Public Sub Manager_Report()
 
 'Declare Variables
 
 Dim main_array As Variant Dim output_array As Variant
 
 'Populate Main Array
 
 main_array = range("A1").CurrentRegion
 
 'Filter the Array for intact 
  output_array = Filter(main_array, "Intact")
 
End Sub

https://learn.microsoft.com/en-us/office/vba/language/reference/user-interface-help/filter-function#:~:text=One%2Ddimensional%20array%20of%20strings%20to%20be%20searched. `Filter` only works on 1-dimensional arrays. For a 2D array you'll need to write a loop. — Tim Williams, Jul 20 '22 at 01:41
I see, thanks for the feedback, do u know of any good guides I can consult for that? — falcon flyer75, Jul 20 '22 at 01:44
@falconflyer75 this is a perfect exercise for you. Write a For Loop with an If Statement. — Jeremy Thompson, Jul 20 '22 at 02:26
Maybe look at - https://stackoverflow.com/questions/10450645/filtering-2d-arrays-in-excel-vba — Tim Williams, Jul 20 '22 at 02:54

score 1 · Answer 1 · answered Jul 20 '22 at 02:36

Option Explicit
Option Base 1
Sub OutputFilterArray()
    
    Dim INarray
    Dim OutArray
    Dim OutArrayFinal
    Dim I As Long
    Dim CNT As Long
    Dim N As Integer
    
    INarray = Range("A3:d" & Range("d" & Rows.Count).End(xlUp).Row).Value
    CNT = 1
    
    ReDim OutArray(UBound(INarray, 1), UBound(INarray, 2))
    For I = 1 To UBound(INarray, 1)
        If INarray(I, 1) = "Intact" Then
            For N = 1 To UBound(INarray, 2)
                OutArray(CNT, N) = INarray(I, N)
            Next N
            CNT = CNT + 1
        End If
    Next I
    
    Range("F3").Resize(UBound(OutArray, 1), UBound(OutArray, 2)) = OutArray
    
End Sub

why am I getting a mismatch error on this VBA Script for an array?

1 Answers1