Re: Any way to do this faster?

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

Hi Jim,

Had a look at this, but I don't think I can use this method.
I need to know if there is a match between elements in array1 and array2 and then when there is a match I need to copy the row of the matching element in array2 to a row with the same number as the matching row in array1 to a third array, array3.
So when the add error occurs I need to know the position of the element in array2 (no problem there as I loop through it adding to the collection) and the position of the matching element in array1.
This last one is the trouble and I just don't think that is possible without a second loop through array1.
This would then lose any speed advantage.
With the array being transferred to an array declared as long it is quick enough. This speeded it up about 7 times.
Thanks in any case for the suggestion.

RBS


"Jim Cone" <jim.coneXXX@xxxxxxxxxx> wrote in message news:%23iBsZNTGGHA.2064@xxxxxxxxxxxxxxxxxxxxxxx 
RBS,
This demo code takes less than second to load and compare.
Jim Cone
San Francisco, USA

'--------------------------------
Sub AreTheyTheSame()
 Dim colNumbers As VBA.Collection
 Dim arrOne() As String
 Dim arrTwo() As String
 Dim lngNum As Long

 Set colNumbers = New VBA.Collection
 ReDim arrOne(1 To 5000)
 ReDim arrTwo(1 To 10000)

'Load arrays
 For lngNum = 1 To 5000
     arrOne(lngNum) = lngNum
 Next
 For lngNum = 1 To 10000
     arrTwo(lngNum) = lngNum + 5000
 Next

'Create a duplicate value
 arrTwo(10000) = 3000

'Load collection and check for duplicates
 For lngNum = 1 To UBound(arrOne)
     colNumbers.Add vbNullString, arrOne(lngNum)
 Next

 For lngNum = 1 To UBound(arrTwo)
    On Error Resume Next
    colNumbers.Add vbNullString, arrTwo(lngNum)
    If Err.Number = 457 Then
       MsgBox arrTwo(lngNum) & " is a duplicate.  "
       Exit For
    End If
 Next

 Set colNumbers = Nothing
End Sub'------------------------------------


"RB Smissaert" <bartsmissaert@xxxxxxxxxxxxxxxx> wrote in message news:O4WBTBTGGHA.3728@xxxxxxxxxxxxxxxxxxxxxxx
Thanks, will have a look at both suggestions.

Actually, I just found a way to speed this up, but that couldn't be seen
from the way I put the question.
Both arrays are variant arrays and I found I needed to do Val(array1element)
= Val(array2element).
This slows it up.
If I make 2 new arrays re-dimmed with the same size, but now declared as
long and transfer the values to
compare to these new arrays and do the comparisons on that it speeds it up a
lot.

I started this routine with match, but found that a double loop was much
faster. Will try again though.

RBS 

.

Relevant Pages

  • Re: Any way to do this faster?
    ... I am not interested in duplicates, but I need to know if there is a match and if so ... what the position is of the matching element in one of the arrays. ... Dim colNumbers As VBA.Collection ... Dim lngNum As Long ...
    (microsoft.public.excel.programming)
  • Re: Any way to do this faster?
    ... This is the sequence as it is now, where RC1 and RC2 are the UBounds of the arrays. ... Dim v1As Long, v2As Long ... ReDim Preserve v1 ... > Dim lngNum As Long ...
    (microsoft.public.excel.programming)
  • Re: Arrays Problem
    ... If the 2 arrays are the same size ... If they aren't the same size you need to copy the elements of array1 ... individually into array2. ... Dim Second() As String ...
    (comp.lang.basic.visual.misc)
  • Re: Arrays please help me
    ... I have two arrays. ... array2 contains 6 elements ... I need to assign the left over 4 elements of array1 into new array. ... Dim array3 ...
    (microsoft.public.dotnet.languages.vb)
  • RE: Cross Function when array crosses above another
    ... The essential concept is for the array1 to be ... their respective arrays and then compared, and not stored on the worksheet ... Sub Crossfunction() ... 'Add the following arrays that are sourced from the worksheet: ...
    (microsoft.public.excel.programming)