VBA runtime error on rowcount

[JoboM]

Hi,

My macro code for rowcount has started producing a runtime error (runtime error 1004: No cells were found). It's only just started flagging this error since the size of the datalog has grown to 430 rows.

There obviously many thousands of rows blank below.

Is there a way to have excel search for longer with rowcount before it times out with runtime error 1004?

Thanks,
JoboM

 

[RedBeard]

Without the code, impossible to tell.

 

[JoboM]

Sorry, RedBeard, I thought there may have been a setting in excel that determined the runtime of such scripts.

Here is the code in question:

Sub LogisticsSchedule_Click()


    Dim mydata As Workbook
    Dim tWB As Workbook
    Dim item1stInj As String
    Dim itemCustomer As String
    Dim i As Integer


    Set tWB = ThisWorkbook
    Set mydata = Workbooks.Open("N:\Logistics\Logistics Tracker\Logistics Tracker 2.xlsm")


    If tWB.Worksheets("Dispensing").Range("G29") > 0 Then
        For i = 5 To 28
            If tWB.Worksheets("Dispensing").Cells(i, 3) > 0 Then
                itemDate = tWB.Worksheets("Dispensing").Range("A3")
                itemRUN1 = tWB.Worksheets("Dispensing").Range("N2")
                itemCustomer = tWB.Worksheets("Dispensing").Cells(i, 1)
                item1stInj = tWB.Worksheets("Dispensing").Cells(i, 12)
                        
                RowCount = mydata.Worksheets("Logistics Tracker").Range("A4").CurrentRegion.Columns(1).SpecialCells(xlBlanks)(1).Row - 4
                With mydata.Worksheets("Logistics Tracker").Range("A4")
                    .Offset(RowCount, 0) = itemDate
                    .Offset(RowCount, 1) = itemCustomer
                    .Offset(RowCount, 2) = "FDG"
                    .Offset(RowCount, 3) = itemRUN1
                    .Offset(RowCount, 8) = item1stInj
                End With
            
            
            End If
        Next i
    End If
    
    If tWB.Worksheets("Dispensing").Range("G29") > 0 Then
        For i = 5 To 28
            If tWB.Worksheets("Dispensing").Cells(i, 3) > 0 Then
                itemDate = tWB.Worksheets("Dispensing").Range("A3")
                itemRUN1 = tWB.Worksheets("Dispensing").Range("N2")
                itemCustomer = tWB.Worksheets("Dispensing").Cells(i, 1)
                item1stInj = tWB.Worksheets("Dispensing").Cells(i, 12)
            
                RowCount = mydata.Worksheets("Customer Manifest").Range("D10").CurrentRegion.Rows.Count
                With mydata.Worksheets("Customer Manifest").Range("D10")
                    .Offset(RowCount, 0) = itemCustomer
                    .Offset(RowCount, 1) = itemRUN1
                    .Offset(RowCount, 2) = "FDG"
                    .Offset(RowCount, 5) = item1stInj
                End With
            
            End If
        Next i
    End If
    End Sub

And the code obviously times out at the rowcount line:

 RowCount = mydata.Worksheets("Logistics Tracker").Range("A4").CurrentRegion.Columns(1).SpecialCells(xlBlanks)(1).Row - 4

Thanks,
JoboM

 

[Norie]

Are you using RowCount for the next empty row?

 

[JoboM]

Hi Norie,

Yes; but only looking in the first column (ie. column A), as there are equations in some of the other columns.

I want the script to rowcount to the first row that has an empty cell in column A (from row 4 onwards).

Thanks,
JoboM

 

[Trebor76]

Duplicate post here

 

[Norie]

Does this code work?

RowCount = Application.Max(4, mydata.Worksheets("Logistics Tracker").Range("A" & Rows.Count).End(xlUp).Row+1)

With mydata.Worksheets("Logistics Tracker")
    .Cells(RowCount, 1) = itemDate
    .Cells(RowCount, 2) = itemCustomer
    .Cells(RowCount, 3) = "FDG"
    .Cells(RowCount, 4) = itemRUN1
    .Cells(RowCount, 9) = item1stInj
End With

 

[JoboM]

Thanks, Norie.

Unfortunately, it is still searching for an entire empty row. However, it does not produce the runtime error! That's a bonus.

This segment of code successfully searches column A, but produces the runtime error:

RowCount = mydata.Worksheets("Logistics Tracker").Range("A4").CurrentRegion.Columns(1).SpecialCells(xlBlanks)(1).Row - 4

Some combination of the two?

Thanks,
JoboM

 

[Norie]

The code I posted only looks in column A.

Which columns do you have formulas in?

Do you have any merged cells?

 

[JoboM]

Hmmm No, there isn't.

But, the strangest thing just happened - it has started working again... I can't understand why. I added in a wait step (for 1 second) and it worked. Then I deleted the wait step, and it still worked...

Now I'm even more confused.