error 13 type mismatch on Variant Array

[GhostEcho]

Hey all,



I have a macro that filters a table, and then fills out a few columns of another table with totals based on the filtered table. Most of the time, it works perfectly, and is tremendously much faster than a sumifs in the worksheet. Unfortunately, out of 160 iterations, it errors out 23 times with error 13, at the red text, where it assigns the column to the array. I've hit a dead end, tried everything I can think of, and searched forums for answers without success. Hopefully one of you can figure it out.

Sub test(CID As Long, myerror As Long)

Dim YTDPart() As Variant, YTDTotal() As Variant, YTDCAT() As Variant, CATPart() As Variant, CATCAT() As Variant, CATPrice() As Variant, CATOrdered() As Variant, CATTotes() As Variant, PriceBase() As Variant, PricePart() As Variant, PriceGroup() As Variant

CATPrice = Range("TOP[Price]").Value2

CATOrdered = Range("TOP[Ordered]").Value2

CATTotes = Range("TOP[Totes]").Value2

GoTo TheRest


skip:

If Err.Number = 13 Then
Cells(myerror + 3, 9) = "ERROR"​
     Else
Cells(myerror + 3, 9) = "DIFFERENT ERROR"​
End If

Exit Sub


TheRest:

     On Error GoTo skip

Sheets("REF").ListObjects("YTD").Range.AutoFilter Field:=2, Criteria1:="=" & CID, Operator:=xlAnd


'These need to be visible only...

YTDPart = Range("YTD[Item]").SpecialCells(xlCellTypeVisible).Value2

YTDTotal = Range("YTD[Ext Sales]").SpecialCells(xlCellTypeVisible).Value2

YTDCAT = Range("YTD[Prod Group Desc]").SpecialCells(xlCellTypeVisible).Value2


'These need to be all

CATPart = Range("TOP[Part]").Value2

CATCAT = Range("TOP[Category]").Value2

PriceBase = Range("Price[Base Price]").Value2

PricePart = Range("Price[Part]").Value2

PriceGroup = Range("Price[PriceList Code]").Value2


For i = 1 To UBound(CATPart)

CATOrdered(i, 1) = (MySumif(YTDTotal(), YTDPart(), CATPart(i, 1)) > 0)

Next i


For i = 1 To UBound(CATPart)

CATTotes(i, 1) = MySumif(YTDTotal(), YTDCAT(), CATCAT(i, 1))

Next i


For i = 1 To UBound(CATPart)

CATPrice(i, 1) = MyVlookup2(CATPart(i, 1), PricePart(), Worksheets(1).Range("AS3").Value, PriceGroup(), PriceBase())

Next i

Sheets("REF").ListObjects("YTD").Range.AutoFilter Field:=2

Range("TOP[Ordered]").Value2 = CATOrdered

Range("TOP[Totes]").Value2 = CATTotes

Range("TOP[Price]").Value2 = CATPrice

'Stop

Erase YTDPart, YTDTotal, CATPart, PricePart

End Sub

The Mysumif and MyVlookup2 are as follows, but they don't error out, so it's just in case you're curious.

Public Function MySumif(Sumit() As Variant, Searchit() As Variant, Mycrit As Variant)

Dim i As Long


MySumif = 0

For i = 1 To UBound(Searchit)

If Searchit(i, 1) = Mycrit Then MySumif = MySumif + Sumit(i, 1)

Next i

End Function

Public Function MyVlookup2(Mycrit As Variant, Searchit() As Variant, Mycrit2 As Variant, Searchit2() As Variant, Myresult() As Variant)


For i = 1 To UBound(Searchit)

If Searchit(i, 1) = Mycrit And Searchit2(i, 1) = Mycrit2 Then MyVlookup2 = Myresult(i, 1)

Next i


End Function

I would truly appreciate any help!

 

[StephenCrump]

You have declared YTDPart as an array:

Dim YTDPart() As Variant

So you will get a type mismatch error if you try to assign its value as a scalar, i.e. a single cell value. Therefore the following line will error if Range("YTD[Item]").SpecialCells(xlCellTypeVisible) contains only a single cell:

YTDPart = Range("YTD[Item]").SpecialCells(xlCellTypeVisible).Value2

Your code will need to allow for Range("YTD[Item]").SpecialCells(xlCellTypeVisible) containing a single cell, as well as multiple cells.

Incidentally, the error line will also only work properly if the filtered rows are contiguous - you can't load discontiguous ranges into a variant in one code line like this.

 

[GhostEcho]

That makes sense... on the first instance of the error, although the filter leaves 60 lines visible, they are not continuous, and the first and second visible lines aren't continuous either. Similarly, future instances also have the same situation.

I sorted the data so that it's continuous and ran it again, now it works perfectly!

Thank you so much!

 

[StephenCrump]

Ok, I'm glad that helped.

Ideally your code should allow for the possibility of only one filtered row, or zero rows. But it sounds like you're happy there will always be 2+ continuous rows.

 

[GhostEcho]

For this particular application I am, due to the fact that the end result is being used to determine what the best selling categories for each customer is, so if they have no sales or only one line of sales, there's no reason to have it calculate. In those instances, I'll just use the overall best selling categories instead. Thank you again!