Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Comparing Multidimensional Arrays?
Hello,
I have coded the following, but am wondering if there is a better way of doing this. Basically i'm comparing two 3D arrays, to see if the data from array1 is in array2. If it isn't, i'm copying the data over to the sheet. heres the code and any help to do this a quicker way would be appreciated. right now the code takes 5 minutes to run when MFArray is 2500 rows and SNArray is 16,000 rows. Thank you. Public Sub CompareArrays() With Application CalcMode = .Calculation .Calculation = xlCalculationManual .ScreenUpdating = False End With Dim MFArray As Variant Dim SNArray As Variant Dim Match As String Dim MFArrayEnd As Integer Dim SNArrayEnd As Integer MFArrayEnd = Sheets("MFrameAENames").UsedRange.Rows.Count SNArrayEnd = Sheets("SalesnetAENames").UsedRange.Rows.Count MFArray = Sheets("MFrameAENames").Range("A2:C" & MFArrayEnd).Value SNArray = Sheets("SalesnetAENames").Range("A2:C" & SNArrayEnd).Value For a = 1 To MFArrayEnd - 1 Match = "No" Do Until Match = "Yes" For b = 1 To SNArrayEnd - 1 If MFArray(a, 1) = SNArray(b, 1) And MFArray(a, 2) = SNArray(b, 2) And MFArray(a, 3) = SNArray(b, 3) Then Match = "Yes" Else End If Next b Exit Do Loop If Match = "No" Then Sheets("MFrameAENames").Select Range("A" & a + 1 & ":S" & a + 1).Select Selection.Copy Sheets("SalesnetAENames").Select Range("A" & ActiveSheet.UsedRange.Rows.Count + 1).Select ActiveSheet.Paste Else End If Next a With Application CalcMode = .Calculation .Calculation = xlCalculationAutomatic .ScreenUpdating = True End With End Sub |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
ReDim, Preserve and Multidimensional arrays | Excel Programming | |||
Declare Multidimensional Arrays | Excel Programming | |||
Declare Multidimensional Arrays | Excel Programming | |||
Multidimensional Arrays - VBA | Excel Programming | |||
Multidimensional Arrays - VBA | Excel Programming |