Variant Array Type Mismatch

[mbpress01]

I was wondering if anyone could help out here. Data is 20 columns (50k rows) with numbers and text (basically stock tickers with market values) and I am trying to use an array to speed up a loop. I already coded the For Next loop but was wondering if an array could improve the time to go through the data. Its seems easy and I thought it would work but I got a type mismatch error on this line

myArray2(a, 1) = myArray(i, 1)

meaning that something is wrong in the array setup. I see the array populated fine in the immediate window but I have no idea why this is happening. After the filtered new array is populated, I want to write it back to the worksheet and any help there would be appreciated.

Sub TestArry()
Dim ActiveSrc As Long
Dim myArray As Variant
Dim myArray2 As Variant
Dim i As Variant
Dim a As Variant
Dim ARow As Long


ARow = Worksheets("Misc").Rows(Rows.Count).End(xlUp).Row
myArray = Sheets("Misc").Range("A1:T" & ARow)
MsgBox "Array populated with " & UBound(myArray) & "entries."

a = 1
For i = 1 To UBound(myArray)
  If myArray(i, 1) = "ABC" Or myArray(i, 4) = "XYZ" Then
    myArray2(a, 1) = myArray(i, 1)
    myArray2(a, 2) = myArray(i, 2)
    myArray2(a, 3) = myArray(i, 3)
    myArray2(a, 4) = myArray(i, 4)
    myArray2(a, 5) = myArray(i, 5)
    myArray2(a, 6) = myArray(i, 6)
    myArray2(a, 7) = myArray(i, 7)
    myArray2(a, 8) = myArray(i, 8)
    myArray2(a, 9) = myArray(i, 9)
    myArray2(a, 10) = myArray(i, 10)
    myArray2(a, 11) = myArray(i, 11)
    myArray2(a, 12) = myArray(i, 12)
    myArray2(a, 13) = myArray(i, 13)
    myArray2(a, 14) = myArray(i, 14)
    myArray2(a, 15) = myArray(i, 15)
    myArray2(a, 16) = myArray(i, 16)
    myArray2(a, 17) = myArray(i, 17)
    myArray2(a, 18) = myArray(i, 18)
    myArray2(a, 19) = myArray(i, 19)
    myArray2(a, 20) = myArray(i, 20)
    a = a + 1
    End If
Next i
MsgBox "Array populated now with " & UBound(myArray2) & "entries."

'How do I write back to sheet in Column AA????


End Sub

Any help if greatly appreciated.

 

[StephenCrump]

Before you can start referencing myArray2(a,1) etc, you need to make myArray2 into an array. Try:

Sub TestArry()
    
    Dim ActiveSrc As Long
    Dim myArray As Variant
    Dim myArray2() As Variant
    Dim i As Long, j As Long
    Dim a As Long
    Dim ARow As Long
        
    ARow = Worksheets("Misc").Range("A" & Rows.Count).End(xlUp).Row 'assuming col A is fully populated?
    myArray = Sheets("Misc").Range("A1:T" & ARow)
    ReDim myArray2(1 To UBound(myArray), 1 To UBound(myArray, 2))
    MsgBox "Array populated with " & UBound(myArray) & "entries."
    
    a = 1
    For i = 1 To UBound(myArray)
        If myArray(i, 1) = "ABC" Or myArray(i, 4) = "XYZ" Then
            For j = 1 To UBound(myArray, 2)
            myArray2(a, j) = myArray(i, j)
        Next j
        a = a + 1
        End If
    Next i
    MsgBox "Array populated now with " & UBound(myArray2) & "entries."


    'How do I write back to sheet in Column AA????


End Sub

This makes myArray2 the same size as myArray1. However, at the end of the loop, you'll have used only a-1 rows in myArray2.

See if you can write it back to column AA. Post back if you need help with this.

 

[StephenCrump]

After the filtered new array is populated, I want to write it back to the worksheet and any help there would be appreciated.

Just re-read your original post. Try:

If a = 1 Then
    MsgBox "Nothing found!"
Else
    Range("AA1").Resize(a - 1, UBound(myArray2, 2)).Value = myArray2
End If

Qualify Range("AA1") with a worksheet name if necessary.

 

[ParamRay]

Hello, I streamlined things a bit, and included code to write back to the worksheet:

Public Sub CopyRows()
  Dim avntCopyValues() As Variant
  Dim avntAllValues() As Variant
  Dim intNumCols As Integer
  Dim lngCounter As Long
  Dim lngLastRow As Long
  Dim i As Integer
  Dim j As Long
  
  On Error GoTo ErrHandler
  With ThisWorkbook.Sheets("Misc")
    lngLastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
    avntAllValues = .Range("A1:T" & lngLastRow).Value
    intNumCols = UBound(avntAllValues, 2)
  
    For j = 1 To UBound(avntAllValues)
      If CStr(avntAllValues(j, 1)) = "ABC" _
      Or CStr(avntAllValues(j, 4)) = "XYZ" Then
        lngCounter = lngCounter + 1
        ReDim Preserve avntCopyValues(1 To intNumCols, 1 To lngCounter)
        For i = 1 To intNumCols
          avntCopyValues(i, lngCounter) = avntAllValues(j, i)
        Next i
      End If
    Next j
    
    With .Range("AA1")
      avntCopyValues = TransposeArray(avntCopyValues)
      .Resize(, intNumCols).EntireColumn.Clear
      .Resize(lngCounter, intNumCols).Value = avntCopyValues
      .Parent.Activate
      .Select
      MsgBox Format(lngCounter, "#,0") & " rows were copied.", vbInformation
    End With
  End With
  
  Exit Sub
ErrHandler:
  MsgBox Err.Description, vbExclamation
End Sub

Private Function TransposeArray(avntSource() As Variant) As Variant()
  Dim intLower1 As Integer
  Dim intUpper1 As Integer
  Dim lngLower2 As Long
  Dim lngUpper2 As Long
  Dim i As Integer
  Dim j As Long
  
  intLower1 = LBound(avntSource, 1)
  intUpper1 = UBound(avntSource, 1)
  lngLower2 = LBound(avntSource, 2)
  lngUpper2 = UBound(avntSource, 2)
  
  ReDim avntResult(lngLower2 To lngUpper2, intLower1 To intUpper1) As Variant
  For j = lngLower2 To lngUpper2
    For i = intLower1 To intUpper1
      avntResult(j, i) = avntSource(i, j)
    Next i
  Next j
  
  TransposeArray = avntResult
End Function

 

[StephenCrump]

Data is 20 columns (50k rows) with numbers and text (basically stock tickers with market values) and I am trying to use an array to speed up a loop.

I really must read the question properly ....

With 50k rows, looping row by row will still be relatively slow.

The faster way to do this would be to use Advanced Filter, then you can either filter in situ, or copy all filtered rows in bulk to a new location.

If you want to try this out, the macro recorder will give you the basic syntax, but post back if you need any help.

 

[mbpress01]

Thank you both so much for responding. I do agree that advanced filter is a good way to go but you must have the criteria on worksheet and for whatever reason I think arrays may be faster. I will test out both. It just seems by the time you set up the criteria, code the logic and then setup the destination range and such, arrays would just seems faster. As an aside, if you have time to respond, generally when would you use one vs the other as I am not clear on which Method is more appropriate for an output needed like the above. Thx again.

 

[StephenCrump]

It would be interesting to run a timer on both .... Sometimes the results can be unexpected, and it may well be that VBA looping is faster than an Advanced Filter approach.

I just tested the looping code from Posts #2/#3 on 50,000 rows, and estimate it took between 1-2 seconds. Even if there were faster approaches, I could live with that.

 

[ParamRay]

Agree that advanced filter is the usual way to go with this kind of action. I also tested the code in #4 and it took just under 1 second for 50,000 rows.