Overflow error

[mysticmario]

Hi,

I have an issue with a code thaht collects and sorts data on the sheet I am using Long instead of Int to describe rows so i dont know how to get rid off it. debugger marks this line: arrPanel = .Range("B1:E" & lrPanel) as the problem, but here is the entire sub code code:

    Dim iRow As Long, lrWs As Long, ws As Worksheet
    Dim lrPanel As Long, lrPanelDate As Long
    Dim wsPanel As Worksheet
  
    Application.ScreenUpdating = False
  
    Set wsPanel = Worksheets("PANEL PODLICZEŃ")
    ' Append data
    For Each ws In Worksheets
        If InStr(1, ws.Name, "Faktury", vbTextCompare) Then
            iRow = wsPanel.Range("D" & Rows.count).End(3).Row + 1
            lrWs = ws.Range("D" & Rows.count).End(3).Row
            ws.Range("C6:C" & lrWs).Resize(, 3).Copy
            wsPanel.Range("C" & iRow).PasteSpecial xlPasteValues
            lrPanel = wsPanel.Cells(Rows.count, "D").End(xlUp).Row
            wsPanel.Range(wsPanel.Cells(iRow, "B"), wsPanel.Cells(lrPanel, "B")).Value = ws.Range("C3").Value
        End If
    Next
  
    ' Sort Data on Date field
    wsPanel.sort.SortFields.Clear
    wsPanel.sort.SortFields.Add2 Key:=Range( _
        "D1:D" & lrPanel), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:= _
        xlSortNormal
    With wsPanel.sort
        .SetRange Range("B:E")              ' XXX Extended sort range
        .Header = xlGuess
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
  
    ' Remove non-data rows
    Dim arrPanel As Variant, arrOut() As Variant
    Dim iOut As Long, i As Long, j As Long

    With wsPanel
        arrPanel = .Range("B1:E" & lrPanel)
        .Range(.Cells(1, "B"), .Cells(lrPanel, "E")).ClearContents
    End With
  
    ReDim arrOut(1 To UBound(arrPanel, 1), 1 To UBound(arrPanel, 2))
  
    For i = 1 To UBound(arrPanel, 1)
        If IsDate(arrPanel(i, 3)) And arrPanel(i, 3) <> 0 And arrPanel(i, 4) <> "" Then
            iOut = iOut + 1
            For j = 1 To UBound(arrPanel, 2)
                arrOut(iOut, j) = arrPanel(i, j)
            Next j
        End If
    Next i
      
    wsPanel.Range("B1").Resize(iOut, UBound(arrPanel, 2)).Value = arrOut
  
    Application.ScreenUpdating = True

Maybe I am mising something?
Please help if possible.

 

[I_know_nuffin]

just a thought but doesnt Dim arrPanel As Variant need Brackets as an array?
so does Dim arrPanel() As Variant, arrOut() As Variant work?

 

[RoryA]

Try using .Range("B1:E" & lrPanel).Value2 rather than just .Range("B1:E" & lrPanel)

 

[mysticmario]

well it helped to go past that but then this change backfires with an eror to this line:
wsPanel.Range("B1").Resize(iOut, UBound(arrPanel, 2)).Value = arrOut
Runtime 1004 Application-defined or object-defined error

 

[RoryA]

Use Value2 again, not Value. I suspect you have one or more cells in that range that appear as #####

Often it's due to a cell that is mistakenly formatted as date but has a large number in it.

 

[mysticmario]

I did do that in the first place, and it didnt help, that's why posted this, I forgot to add that - my mistake.
as for the ##### - yes i probably do have that this is a large data collection, which si being cleaned and sorted.

 

[RoryA]

Have you checked that iOut isn't 0?

 

[I_know_nuffin]

looking at Excel VBA Resize Range - Automate Excel

the below seems to be what your doing

Sub WriteArray()
' Read the data to an array
data = Range("A1:E10").Value
' Resize the output range and write the array
Worksheets("Output").Range("A1").Resize(UBound(data, 1), UBound(data, 2)).Value = data
End Sub

so Worksheets("Output").Range("A1").Resize(UBound(data, 1), UBound(data, 2)).Value = data

converts to
wsPanel.Range("B1").Resize(UBound(arrOut, 1, UBound(arrOut, 2)).Value = arrOut

 

[mysticmario]

Have you checked that iOut isn't 0?

it is.
I dont fully understand this code it's too advanced for my current VBA knowledge.
Someone from this forum from Tawiwan wrote it for me, unfortunately we had a little falling out because he wanted to use this code as a bargaining chip, to get me to pay him for further assistance.
The code worked for like 8 months but now it stopped working.
What would be iOut variable in this code?

 

[RoryA]

It's a counter. If it's 0, then none of the data met the conditions in the code, so there is no point in trying to populate the range anyway.