VBA Arrays

[SharmaAntriksh]

Why do i get an error is the last line of this code?

Sub ExtractingDatawithArrays()
  
  Dim TeamStatus() As Variant, ExtractData() As Variant, Counter As Long
  Dim i As Long, k As Long
  
  TeamStatus = Sheet1.Range("A1").CurrentRegion
  
  For i = 2 To UBound(TeamStatus, 1)
    
    If TeamStatus(i, 30) = "Antriksh Sharma" Then
      Counter = Counter + 1
    
    ReDim Preserve ExtractData(1 To UBound(TeamStatus, 2), 1 To Counter)
    
    For k = LBound(TeamStatus, 2) To UBound(TeamStatus, 2)
        ExtractData(k, Counter) = TeamStatus(i, k)
    Next k
    
    End If
  Next i
  
  Sheet2.Range("A1", Sheet2.Range("A1").Offset(UBound(ExtractData, 2) - 1, UBound(ExtractData, 1) - 1)).Value = WorksheetFunction.Transpose(ExtractData)


End Sub

 

[Fluff]

What error do you get?

 

[SharmaAntriksh]

Run Time Error 13: Type Mismatch

 

[Fluff]

It maybe that you hit the limit for transpose.
Try

Sub ExtractingDatawithArrays()
  
  Dim TeamStatus() As Variant, ExtractData() As Variant, Counter As Long
  Dim i As Long, k As Long
  
  TeamStatus = Sheet1.Range("A1").CurrentRegion
  ReDim ExtractData(1 To UBound(TeamStatus), 1 To UBound(TeamStatus, 2))
  For i = 2 To UBound(TeamStatus, 1)
    
    If TeamStatus(i, 30) = "Warwickshire" Then
      Counter = Counter + 1
    
    For k = LBound(TeamStatus, 2) To UBound(TeamStatus, 2)
        ExtractData(Counter, k) = TeamStatus(i, k)
    Next k
    
    End If
  Next i
  
  Sheet2.Range("A1", Sheet2.Range("A1").Resize(UBound(ExtractData, 1) - 1, UBound(ExtractData, 2) - 1)).Value = ExtractData


End Sub