Getting results from a dynamic dimension array

[remeng]

Hi All;

I am trying to return values from an array based on a search term. The array is being captured correctly but I can't search the array return correctly. When I hardcode the array, the search does work.

I get a type mismatch 13 when I run the code.

Worksheet = "data"
dynamic table = "data"
Column A has dynamic array
Column A header = ""#MODEL_NUMBER""
search term = "RP"

Values in Column A
----------

#MODEL_NUMBER
RP060324-1
A
B
C
I
B
T
RP060324-1
RP060324-1

------------

Sub data_table_replacement_part_search()

Dim replacement_part_array As Variant



model_number_row_count = ThisWorkbook.Sheets("data").Range("data").ListObject.ListRows.Count

model_number_index = ThisWorkbook.Sheets("data").ListObjects("data").ListColumns("#MODEL_NUMBER").Index

replacement_part_array = ThisWorkbook.Worksheets("data").Range(Cells(2, model_number_index), Cells(model_number_row_count, model_number_index))








'replacement_part_array = Array("RP1", "RP2", "BOB")

'replacement_part_array = Array(replacement_part_array_results)


search_term = "RP"



filter_term = Filter(replacement_part_array, search_term)
    
    For Each x In filter_term
    
       MsgBox x
    
    Next


End Sub

What am I doing wrong?

Thanks,

Mike

 

[Andrzejek]

If your [tt]replacement_part_array[/tt] populates OK, what do you get in the Immediate Window if you do:

Dim X As Integer
For X = LBound(replacement_part_array) to UBound(replacement_part_array)
    Debug.Print replacement_part_array(X)
Next X

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[Andrzejek]

But if you insist on copying your data into an array (it is already in the array in Excel) and searching array...

How about:

Option Explicit

Sub data_table_replacement_part_search()
Dim ary_repl_part() As String
Dim int_Rows As Integer
Dim X As Integer
Dim strFind As String

strFind = InputBox("What are you looking for?", "Search")
int_Rows = Sheets("data").UsedRange.Rows.Count

ReDim ary_repl_part(int_Rows - 2)

For X = 2 To int_Rows
    ary_repl_part(X - 2) = Sheets("data").Cells(X, 1).Value
Next X

For X = LBound(ary_repl_part) To UBound(ary_repl_part)
    If InStr(ary_repl_part(X), strFind) Then [green]   'case sensitive
    'If InStr(UCase(ary_repl_part(X)), UCase(strFind)) Then    'non-case sensitive[/green]
        MsgBox ary_repl_part(X) & " in row " & X + 2
    End If
Next X

End Sub

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson