Run-time error 1004: No cells were found

[JoboM]

Hi there gurus,

I have data being sent to a trending spreadsheet, which of course has been building in size over time (now up to about 430 rows), and just this week the macro has stopped working.

I am now presented with the following error code:

"Run-time error 1004: No cells were found"

Can someone help? Why has it only occurred now? And how do I fix it?!?!

Code below:

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

The error occurs at this line:

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

And the data table has its first row at A6.

Thanks

 

[baitmaster]

I'm guessing it can't find any blanks in the current region. I'd suggest you look at better ways of referencing your cells, as there's probably a few issues with this approach

First you don't really want to be referring to Excel range references e.g. "A4" as any changes to the worksheet can create a disconnect with your code. I'd advise you to look at using named ranges instead

Second, you're relying on finding blank cells, and your code will crash if you can't find any. Is there a better criteria you can use? e.g. the next non-blank cell below your start point? Can you state what exactly you're trying to refer to?

My hunch is you want to count continuous rows of data from row 6 downwards so you could use something like

Range("A6").End(xlDown).Row - Range("A6").Row + 1

 

[JoboM]

Hi Baitmaster,

Thanks for replying.

A blank cell in column A is the main criteria. This spreadsheet is just a data log that is intended to grow over time, for the sole purpose of trending. As such, I just want the code to search for the first empty cell in column A (as other columns are prefillled, so cannot search for empty rows).

I'm perplexed as to why the the code has only now stopped working. Is it possible that the range its searching over has become too large?

Is there a better way to rowcount?

Thanks,
JoboM.

 

[Trebor76]

Duplicate post here

 

[RoryA]

Please do not post the same question multiple times. Thread closed.