• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Error in VBA code at .Resize = Application.Transpose (Tmp)

kuldeepjainesl

kuldeepjainesl

Member
Hello, i am using this code to filter the desired row from one sheet to another. Now my code is giving me error on the line below while pasting data. i need help to understand & sort out.

Code: 
With WherePaste
        .Resize(Row_Counter, Number_Of_Columns).Value = Application.Transpose(Tmp)

Whole code is as follows :

Code: 
Public Sub Filter_Failure_Data()
Dim LastLig As Long, Number_Of_Rows As Long, Row_Counter As Long, i As Long
Dim Row_Array As Variant, Record_Array As Variant, Tmp() As Variant
Dim Customer_Name As String, Additional_Check As String
Dim Number_Of_Columns As Integer, j As Integer
Dim Check_Date As Double
Dim WherePaste As Range
Dim User_Input As Byte

Application.ScreenUpdating = True
With ThisWorkbook.Worksheets("Failure data").Range("Failure_Data")
    Number_Of_Rows = .Rows.Count
    Record_Array = ThisWorkbook.Worksheets("Dashboard").Range("A21:G21").Value
    Number_Of_Columns = .Columns.Count
    Row_Array = .Value

    For i = 2 To Number_Of_Rows
        Application.StatusBar = Format(i / Number_Of_Rows, "0%")
        Customer_Name = Row_Array(i, 33)
        Check_Date = CDbl(Row_Array(i, 4))
        Additional_Check = Row_Array(i, 15) ' Additional check row will be defind (Here it is Cat-code)

        If Customer_Name = Record_Array(1, 1) Then
            If Check_Date >= CDbl(Record_Array(1, 3)) And Check_Date <= CDbl(Record_Array(1, 5)) Then
                If Record_Array(1, 7) = "" Or Left(Additional_Check, 10) = Record_Array(1, 7) Then
                    Row_Counter = Row_Counter + 1
                    ReDim Preserve Tmp(1 To Number_Of_Columns, 1 To Row_Counter)
                    For j = 1 To Number_Of_Columns
                        Select Case j
                            Case 4, 44: Tmp(j, Row_Counter) = CDbl(Row_Array(i, j))
                            Case Else: Tmp(j, Row_Counter) = Row_Array(i, j)
                        End Select
                    Next j
                End If
            End If
        End If
    Next i
End With

If Row_Counter > 0 Then
    With ThisWorkbook.Worksheets("Filtered Failure Data")
        User_Input = MsgBox("Do you wish to APPEND to earlier data ( Y/N ) ; NO means earlier data will be overwritten !", vbYesNo)
        If User_Input = vbYes Then
            Set WherePaste = .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0)
        Else
            LastLig = .Cells(.Rows.Count, "A").End(xlUp).Row
            If LastLig >= 2 Then .Rows("2:" & LastLig).ClearContents
            Set WherePaste = .Range("A2")
        End If
    End With
    With WherePaste
        .Resize(Row_Counter, Number_Of_Columns).Value = Application.Transpose(Tmp)
        .Offset(0, 3).Resize(Row_Counter, 1).NumberFormat = "dd/mm/yy"    'column 4 is date
        .Offset(0, 43).Resize(Row_Counter, 1).NumberFormat = "dd/mmm/yyyy"    'column 44 is date
    End With
    Set WherePaste = Nothing
    MsgBox "Procedure Over , " & Row_Counter & " records copied / pasted"
Else
    MsgBox "Nothing copied / pasted"
End If
Application.StatusBar = False
End Sub
 
Hi Kuldeep ,

As a first check , see if you are hitting the limit ; type in :

excel vba application.transpose limit

in Google and go through the relevant result(s).

If you are not hitting the limit(s) , you can look at other possibilities.

Narayan
 
Code is running correctly on my machine. I'd put a stop or debug message right before that line, and check that the Row_Counter and Number_of_Columns are matching the size of Tmp.
 
i found that

The maximum number of elements in the array is limited by available memory or the Excel worksheet maximum size (65536 rows X 256 columns). However, the maximum number of elements in the array that you can pass to Excel using the Excel Transpose function is 5461. If you exceed this limit, you receive the following error message: Run-time error '13': Type Mismatch
total size of row where the code is able to run is 61900 Row X 50 Coloum but i add the database and now the row count is 116000 Row X 50 Coloum.

i am getting also getting Run-time error '13': Type Mismatch. How i can move ahead. Any help is appriciated
 
Luke , Row_Counter and Number_of_Columns are just the same as the actual records.

For example total row are 100,000 and matching row are 10,000 then the Row_Counter and Number_of_Columns show the 10,000 row and 50 coloum in debug
 
Taking a broader look at what you are trying to do, perhaps you could run an AdvancedFilter rather than copying all the records via VB? I'm guesing that you have some sort of criteria range already setup, and you're transferring the results from one sheet to another.
 
Right, but auto filter is not a option to choose with me. However it is it can be achieved via VB, my objective will be served.

Now as it seems that i am hitting the limit so what are the other ways of achieving this in this code. Please note that data size is likely to be grow on a rapid pace.

Will the upgrade of my office 2010, 32 bit version to 64 bit will help ?
 
Last edited:
Hello Luke, Can you help...in sorting this out.

One Strange behaviour i am getting is indicating somewhere else :

Lets Say :

There are two criteria A and B
Total data base 100K Row and 50 coloum
For A condition database has 1800 matching record and able to process
For B condition be there is only 300 record and failing in type mismatch

VBA is not actually my domain thus struggling a lot in this.
 
When you say "failing in type mismatch", is that a VB error, like you stated above where the array was hitting a limit?

Would it be possible to
a) Have an example workbook showing your layout
b) State what the overall goal is, e.g., copy all records that match X to sheet Y
 
I have figured it. i removed all data and filled with a known value and now i am able to run the code. this indicate that there is something wrong in the data itself and need cleaning. sorry to bother you.
 
You must log in or register to reply here.
Back
Top