search through an array of 40K rows

[hrm1220]

I'm hoping someone can point me in the right direction. I have 2 tables that are 30K+ rows.
I've put them in arrays.
1. need to find unique ID from myarray in the 2ndarray
2. if finds unique ID in 2ndarray
a)need to check to see if certain criteria match
b) if doesn't match criteria find the next unique ID in the 2ndarray
c) if can't find another recordset in the 2ndarray then get the next unique ID from myarray

Dim Time_col As Integer
Dim myArray() As Variant, 2ndArray() As Variant, varData3() As Variant
Dim startact As String, m As Integer, i As Integer, t As Integer
Dim arry() As Integer, r As Range, iCol As Integer
m = 2
ReDim varData3(13, 0)
Set macroworkbook = ActiveWorkbook
macroworkbook.Activate

Sheets("Test").Select
     ' Find the FIRST real row
    Firstrow = 2
    Firstcol = 1
  ' Find the LAST real column
    endcol = ActiveSheet.Cells.Find(What:="*", _
      SearchDirection:=xlPrevious, _
      SearchOrder:=xlByColumns).Column
   'Find the LAST real row
    endrow = ActiveSheet.Cells.Find(What:="*", _
      SearchDirection:=xlPrevious, _
      SearchOrder:=xlByRows).Row

    endrowscan = Sheets("Test2").Cells.Find(What:="*", _
      SearchDirection:=xlPrevious, _
      SearchOrder:=xlByRows).Row
 'combines data
Time_col = Cells.Find("START_EVENT_TIME").Column
intTriggerCount = 0
i = 2
iCol = 1

myArray() = Range(Cells(Firstrow, iCol), Cells(endrow, endcol)).Value
Sheets("Test2").Select
2ndArray() = Range(Cells(Firstrow, iCol), Cells(endrowscan, endcol)).Value
For m = 1 To 100
test1 = myArray(m, 1)
For i = 20300 To 23000
    If 2ndArray(i, 1) = test1 Then
     If 2ndArray(i, Time_col) > myArray(m, Time_col) _
        And 2ndArray(i, Time_col) < myArray(m, Time_col + 1) Then

            If intTriggerCount = 0 Then
                    varData3(0, intTriggerCount) = 2ndArray(i, 6) & "/" & myArray(m, 6) '
                    varData3(1, intTriggerCount) = myArray(m, 9) '
                    varData3(2, intTriggerCount) = myArray (m, 13)
                    varData3(3, intTriggerCount) = myArray (m, 16) 
                    varData3(4, intTriggerCount) = i & ";" & m 'Row Number
                    varData3(5, intTriggerCount) = myArray (m, 14)
                    varData3(6, intTriggerCount) = myArray (m, 11) 
                    varData3(7, intTriggerCount) = myArray (m, 8) 
                    varData3(8, intTriggerCount) = Right(myArray(m, 6), 3) 
                    varData3(9, intTriggerCount) = myArray(m, Time_col) 'Start Time 1
                    varData3(10, intTriggerCount) = myArray(m, Time_col + 1) 'End Time 1
                    varData3(11, intTriggerCount) = 2ndArray(i, Time_col) 'Start Time 2
                    varData3(12, intTriggerCount) = 2ndArray(i, Time_col + 1) 'End Time 2
                    varData3(13, intTriggerCount) = myArray(m, 15)

                    intTriggerCount = intTriggerCount + 1
                Else
                    ReDim Preserve varData3(13, UBound(varData3, 2) + 1)
                    varData3(0, intTriggerCount) = 2ndArray(i, 6) & "/" & myArray(m, 6) '
                    varData3(1, intTriggerCount) = myArray(m, 9) '
                    varData3(2, intTriggerCount) = myArray(m, 13)
                    varData3(3, intTriggerCount) = myArray(m, 16) 
                    varData3(4, intTriggerCount) = i & ";" & m 'Row Number
                    varData3(5, intTriggerCount) = myArray(m, 14)
                    varData3(6, intTriggerCount) = myArray(m, 11) 
                    varData3(7, intTriggerCount) = myArray(m, 8) 
                    varData3(8, intTriggerCount) = Right(myArray(m, 6), 3) 
                    varData3(9, intTriggerCount) = myArray(m, Time_col) 'Start Time 1
                    varData3(10, intTriggerCount) = myArray(m, Time_col + 1) 'End Time 1
                    varData3(11, intTriggerCount) = 2ndArray(i, Time_col) 'Start Time 2
                    varData3(12, intTriggerCount) = 2ndArray(i, Time_col + 1) 'End Time 2
                    varData3(13, intTriggerCount) = myArray(m, 15)
                    intTriggerCount = intTriggerCount + 1
                    End If


End If
End If
Next
Next
With Sheets("Inputs Table")
For t = 0 To intTriggerCount
Cells(2 + t, 1).Value = varData3(0, t)
Cells(2 + t, 2).Value = varData3(1, t)
Cells(2 + t, 3).Value = varData3(2, t)
Cells(2 + t, 4).Value = varData3(3, t)
Cells(2 + t, 5).Value = varData3(4, t)
Cells(2 + t, 6).Value = varData3(5, t)
Cells(2 + t, 7).Value = varData3(6, t)
Cells(2 + t, 8).Value = varData3(7, t)
Cells(2 + t, 9).Value = varData3(8, t)
Cells(2 + t, 10).Value = varData3(9, t)
Cells(2 + t, 11).Value = varData3(10, t)
Cells(2 + t, 12).Value = varData3(11, t)
Cells(2 + t, 13).Value = varData3(12, t)
Cells(2 + t, 14).Value = varData3(13, t)
Next
End With

End sub

thank you for the help

 

[CautionMP]

A range is an array (which you seem to know based on your sample) so why not skip the array building all together and just work with the range objects?

I'm having a hard time following all the range/array relationships but I think this should be pretty close. If so I leave the rest of the calculations to you.

Sub hrm1220()
  Dim wksInputTable As Worksheet
  Dim rngTest As Range, rngTest2 As Range
  Dim rngRowTest As Range
  Dim lngColumnTime As Long, lngRowTest2 As Long
  Dim lngRowInputTableOut As Long
  Dim intTriggerCount As Integer
  
  Set wksInputTable = ThisWorkbook.Worksheets("Inputs Table")
  Set rngTest = ThisWorkbook.Worksheets("Test").UsedRange
  Set rngTest2 = ThisWorkbook.Worksheets("Test2").UsedRange
  lngColumnTime = Cells.Find("START_EVENT_TIME").Column
  
  For Each rngRowTest In rngTest.Rows
    lngRowInputTableOut = 2
    For lngRowTest2 = rngTest2.Row To rngTest2.Row + rngTest2.Rows
      If rngTest2.Cells(lngRowTest2, 1).Value = rngTest.Columns(1).Value Then
        If rngTest2.Cells(lngRowTest2, lngColumnTime).Value > rngTest.Columns(lngColumnTime).Value _
        And rngTest2.Cells(lngRowTest2, lngColumnTime).Value > rngTest.Columns(lngColumnTime + 1).Value Then
          wksInputTable.Cells(lngRowInputTableOut, 1) = rngTest2.Cells(lngRowTest2, 6) & "/" & rngRowTest.Columns(6).Value
          wksInputTable.Cells(lngRowInputTableOut, 2) = rngRowTest.Columns(6).Value
          wksInputTable.Cells(lngRowInputTableOut, 3) = rngRowTest.Columns(9).Value
          '...
          lngRowInputTableOut = lngRowInputTableOut + 1
        End If
      End If
    Next lngRowTest2
  Next rngRowTest
  
  Set rngTest = Nothing
  Set rngTest2 = Nothing
  Set wksInputTable = Nothing
End Sub

Hope this helps,
CMP

[small]For the best results do what I'm thinking, not what I'm saying.[/small]
(GMT-07:00) Mountain Time (US & Canada)

 

[macropod]

Hi cautionMP,

so why not skip the array building all together and just work with the range objects?

One reason for not doing so is if you need to acquire the data from a large number of external text files. Loading a text file onto a worksheet, processing and clearing before loading the next file is much slower than working with an array in memory and, more importantly, Excel gets progressively slower at processing each successive file.


Cheers
[MS MVP - Word]

 

[SkipVought]

"...if you need to acquire the data from a large number of external text files..."

Under those circumstances, I might consider using MS Query or ADO to query those text files. Certainly would be much less VBA required.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]