Type Mismatch Error

[oHoi99]

Hi, below is a part of my code. I'm trying to extract data from SQL, store them in an array, transpose it (no problem up to here) and lastly paste them out starting from cell A1. But it keeps showing Type Mismatch Error at the line I bolded. Could anyone help me please?

Sub PullData()
.
.
.'(Other part of my code including calling my GetData() Function )
.
.

'|---------------------------------------------------|
'|Inserting retrieved data into "Datasheet" worksheet|
'|---------------------------------------------------|
With ThisWorkbook.Worksheets("Datasheet")
   .Range("A2:J" & .Cells(.Rows.count, "J").End(xlUp).Row).ClearContents
   .Range("A1").Resize(UBound(results, 1), UBound(results, 2)).Value = results
   End With
End Sub


Function GetData()
.
.
.'(Other part of my code)
.
.
    '|------------------------------|
    '|Storing fields name into array|
    '|------------------------------|
    'On Error GoTo TitleError
    Dim result() As Variant
    ReDim result(1 To rs.Fields.count, 1 To 2)
    For i = 1 To rs.Fields.count
        result(i, 1) = rs.Fields(i - 1).Name
    Next


    '|--------------------------------------|
    '|Storing data into array if data exists|
    '|--------------------------------------|
    'On Error GoTo DataError
    If Not rs.EOF Then
        Dim RAW As Variant
        RAW = rs.GetRows()

        rs.Close
        conn.Close
        Set rs = Nothing
        Set conn = Nothing

        ReDim Preserve result(1 To UBound(result, 1), 1 To UBound(RAW, 2) + 2)
        For j = 0 To UBound(RAW, 2)
            For i = 0 To UBound(RAW, 1)
                result(i + 1, j + 2) = RAW(i, j)
            Next
        Next
    End If
  
  
    '|--------------------------------------|
    '|--------Transposing the Array---------|
    '|--------------------------------------|
 
    Dim numRows As Long
    Dim numCols As Long
    numRows = UBound(RAW, 1)
    numCols = UBound(RAW, 2)
  
    Dim transposedArray() As Variant
    ReDim transposedArray(1 To UBound(RAW, 2), 1 To UBound(RAW, 1))
  
    Dim r As Long, c As Long
    For r = 1 To UBound(RAW, 2)
        For c = 1 To UBound(RAW, 1)
            transposedArray(r, c) = RAW(c, r)
        Next c
    Next r
  
    results = transposedArray

End Function

 

[rondeondo]

Bold text is not showing in the code section. Please add a comment to show the line of concern

 

[oHoi99]

Bold text is not showing in the code section. Please add a comment to show the line of concern

Apologies for that. The line is ".Range("A1").Resize(UBound(results, 1), UBound(results, 2)).Value = results"

Please refer to the code below:

Sub PullData()
.
.
.'(Other part of my code including calling my GetData() Function )
.
.

'|---------------------------------------------------|
'|Inserting retrieved data into "Datasheet" worksheet|
'|---------------------------------------------------|
With ThisWorkbook.Worksheets("Datasheet")
   .Range("A2:J" & .Cells(.Rows.count, "J").End(xlUp).Row).ClearContents
   .Range("A1").Resize(UBound(results, 1), UBound(results, 2)).Value = results 'this line of code is showing the Error
   End With
End Sub


Function GetData()
.
.
.'(Other part of my code)
.
.
    '|------------------------------|
    '|Storing fields name into array|
    '|------------------------------|
    'On Error GoTo TitleError
    Dim result() As Variant
    ReDim result(1 To rs.Fields.count, 1 To 2)
    For i = 1 To rs.Fields.count
        result(i, 1) = rs.Fields(i - 1).Name
    Next


    '|--------------------------------------|
    '|Storing data into array if data exists|
    '|--------------------------------------|
    'On Error GoTo DataError
    If Not rs.EOF Then
        Dim RAW As Variant
        RAW = rs.GetRows()

        rs.Close
        conn.Close
        Set rs = Nothing
        Set conn = Nothing

        ReDim Preserve result(1 To UBound(result, 1), 1 To UBound(RAW, 2) + 2)
        For j = 0 To UBound(RAW, 2)
            For i = 0 To UBound(RAW, 1)
                result(i + 1, j + 2) = RAW(i, j)
            Next
        Next
    End If
 
 
    '|--------------------------------------|
    '|--------Transposing the Array---------|
    '|--------------------------------------|
 
    Dim numRows As Long
    Dim numCols As Long
    numRows = UBound(RAW, 1)
    numCols = UBound(RAW, 2)
 
    Dim transposedArray() As Variant
    ReDim transposedArray(1 To UBound(RAW, 2), 1 To UBound(RAW, 1))
 
    Dim r As Long, c As Long
    For r = 1 To UBound(RAW, 2)
        For c = 1 To UBound(RAW, 1)
            transposedArray(r, c) = RAW(c, r)
        Next c
    Next r
 
    results = transposedArray

End Function

 

[rondeondo]

is the intent of that line to set a paste cell area? What values do you get from the two UBOUND() statements? Can be worth stepping through the code to see. I'd guess there may be a limit to this function for how big a range you can specify.

 

[RoryA]

You should post the full pullData code. Most likely cause for that error is that results is not an array.