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.

 

[mysticmario]

Previously the data all these sheets and ranges was imported to the sheet but it was a mess it had invalid dates, or had the date cell be completely empty or even there was som3thins completely different than date as a value. This time around - after the changes when i get to an error no data is being shown on the sheet, so there is no data to check through conditions.
Before i came here for help and before we switched the arrPanel to value2 i had the overflow error but the data was being imported, just the "cleaning" part wasnt working.

 

[RoryA]

iOut only gets incremented if this line is true:

If IsDate(arrPanel(i, 3)) And arrPanel(i, 3) <> 0 And arrPanel(i, 4) <> "" Then

if iOut is 0, then no lines in the input data met those three criteria. Your code should really test if iOut is 0 before trying to output an empty array to a range 0 rows high (which is what causes the 1004 error).

 

[mysticmario]

There are no lines in the inpuit data because the enitre arrPanel range is empty.
Here's what I get when i just end the sub after i get an overlow error using .value

 

[mysticmario]

and this is what i end up with afetr i change .value into .value2 - empty data range

 

[RoryA]

We already sorted out the overflow error, did we not? If arrPanel is empty, there is no point in processing it at all.

 

[mysticmario]

a

We already sorted out the overflow error, did we not? If arrPanel is empty, there is no point in processing it at all.

After sorting out overflow error, data is not being pulled at all. arrPanel will never be empty in my case because this is a list of invoices sorted between different project sheets. It's just being pulled togetehr witha lot of junk data

 

[RoryA]

Ah, OK. The issue is that Value2 will pull dates through as serial numbers, and IsDate will not return True for those.

What kind of non-date data are you expecting in column C?

 

[mysticmario]

this is what a proper data after cleaning and sortign looks like:

but the data is being pulled from a sheet that looks like this:

as you can see data pulled into column C is a invoice number but the "source sheet" in the same column as invoice number (C), also has text such as "Last period Net" or other, which is being pulled along side the invoice number, to add to that also multiple instances of "bad" data (empty cells, wrong dates etc) is being pulled while the code goes through the entire workbook.
some examplr of bad data can be seen here:

 

[RoryA]

I suspect the issue is being caused by the total calculations at the bottom of the source sheets and column D on the panel sheet having a date format applied. Try amending the first section of code to:

    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 PODLICZEN")
    wsPanel.Range("D:D").ClearFormats
    ' 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 xlPasteValuesAndNumberFormats
            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

which will clear the date formats and paste the source number formatting. Then go back to using Value instead of Value2.

 

[mysticmario]

It worked like a charm, thank you.