Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Getting results from a dynamic dimension array

Status
Not open for further replies.
remeng

remeng

Technical User
Jul 27, 2006
518
US
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

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

Code: 
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

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

Code: 
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
 
Upvote 0 Downvote
But if you insist on copying your data into an array (it is already in the array in Excel) and searching array...

Model_mvyb19.png


How about:

Code: 
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
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

K
  • Question
How to resolve define worksheets array
Replies
1
Views
269
combo
combo
remeng
  • Locked
  • Question
Delete dynamic table rows without removing password protection
Replies
7
Views
232
remeng
remeng
dgillz
  • Locked
  • Question
Back coloring an entire row if a duplicate is found in a dynamic range 1
Replies
18
Views
316
SkipVought
S
remeng
  • Locked
  • Question
CTRL + BREAK from second macro button
Replies
5
Views
277
strongm
strongm
remeng
  • Solved
Run-time error '429': ActiveX component can't create object From Excel to Word
Replies
9
Views
487
remeng
remeng

Part and Inventory Search

Sponsor

Back
Top