Excel Run-Time Error 1004 No cells were found

[Add365]

Hi everyone,

I know this has been asked before but I cant work out from looking at previous threads how to fix my issue.
I am running a stock take macro where I am copying data from an invoice over to a stock out list but in one column I want it to fill in the blank cells with the data above. If I run the macro with 2 rows of data coming accross it comes up Run-time error 1004 no cells was found, but the macro has actually worked and I have to click end. If I run the macro with say 3 or 4 rows of data coming accross it works fine and no errors.
I have tried putting On Error Resume Next & On Error GoTo 0 but then this just skips the code and the macro dosent work properley.

Please can you help me out, below is my vba code and thank you for your support & help.

Dim lr As Long
Sheets("Invoice").Select
Range("C6").Select
Selection.Copy
Sheets("Stock Out").Select
lr = Range("A" & Rows.Count).End(xlUp).Row

Range("A1:A" & lr).SpecialCells(xlCellTypeBlanks).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

 

[DanteAmor]

Yes I did try your code and it worked the same as mine but when I put only 2 items on the invoice the macro couldnt find any blank cells to fill in on the stock out sheet so as you said it pasted the data on an extra row down

I am not following you. Forget the code for a moment.

I understand that you want to copy data from sheet "invoice" and paste into sheet "Stock Out".
If you are going to give examples, use the XL2BB tool.

So, in the sheet "Stock Out" can there be empty rows?
If in the "Stock Out" sheet there are only two empty rows, let's say rows 5 and 6 are empty, row 7 has data, but you want to copy 3 records from the "Invoice" sheet, what would you need: invoice record 1 in row 5, invoice record 2 in row 6 and record 3 in row 8?

And if there are no empty intermediate rows, what should the code do?

 

[johnnyL]

Hi,

Yes I did try your code and it worked the same as mine but when I put only 2 items on the invoice the macro couldnt find any blank cells to fill in on the stock out sheet so as you said it pasted the data on an extra row down which is not really what I want to happen.

If there are no blank cells to put the data in , and you stated that you don't want the data added to the next row down, what would you like to happen?

 

[Add365]

@Add365 have you tested that code you just posted?

Yes been using it for around 2 weeks and all fine apart from the line of code I started this thread with

Dim lr As Long
Sheets("Invoice").Select
Range("C6").Select
Selection.Copy
Sheets("Stock Out").Select
lr = Range("A" & Rows.Count).End(xlUp).Row

Range("A1:A" & lr).SpecialCells(xlCellTypeBlanks).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

As when I only transfer over 2 lines of data it comes up with error and "on error resume next" is not working for me.

 

[johnnyL]

How does your code not error at the following:

End If    ' <---
End Sub

Please answer Post #12 also.

 

[Add365]

I am not following you. Forget the code for a moment.

I understand that you want to copy data from sheet "invoice" and paste into sheet "Stock Out".
If you are going to give examples, use the XL2BB tool.

So, in the sheet "Stock Out" can there be empty rows?
If in the "Stock Out" sheet there are only two empty rows, let's say rows 5 and 6 are empty, row 7 has data, but you want to copy 3 records from the "Invoice" sheet, what would you need: invoice record 1 in row 5, invoice record 2 in row 6 and record 3 in row 8?

And if there are no empty intermediate rows, what should the code do?

Yes so basically what ever is on the invoice I want transfered over to the stock out sheet, there shouldnt be any empty cells or colums so if we start a fresh I want A1 all the way to L1 filled with data and however many lines of data are on the invoice I want that transfered to the stock out sheet and then to carry on when i create a new invoice and click the macro button.

Rows 7 wouldnt have data in if rows 5 & 6 are empty as rows 5 & 6 would fill up first then go to row 7 as so on.

The only reason Cell A2 or 3 or 4 would be empty is the way I am transfering over the company name then from the invoice as I only have 1 cell on the invoice with the company name but I might need to paste that down column A2,A3,A4 to fill in the gaps depending how many rows of data from the invoice transferred over.

Hope this makes sense, there is probably a better way of transferring the data from the invoice to the stock out sheet, if you have any recommendations let me know.

Thanks

 

[Add365]

If there are no blank cells to put the data in , and you stated that you don't want the data added to the next row down, what would you like to happen?

Hi,

if there is no blank cells to fill in with data I want the macro just to skip that part but when I put in "on error resume next" the macro skips pasting the original data all together

 

[Add365]

How does your code not error at the following:

End If    ' <---
End Sub

Please answer Post #12 also.

I have a message box pop up at the end of the macro under certain circumstances that's why End if is in there, I just deleted that part of the code as it has business details that come up so didnt want to show that on a public thread. Thanks

 

[johnnyL]

Hi,

if there is no blank cells to fill in with data I want the macro just to skip that part but when I put in "on error resume next" the macro skips pasting the original data all together

Did you also place 'On Error GoTo 0' after the line that errors? In other words:

On Error Resume Next
Line that errors
On Error GoTo 0

 

[johnnyL]

See if this does what you want:

    If Sheets("Stock Out").Application.WorksheetFunction.CountBlank(Sheets("Stock Out").Range("A1:A" & lr)) > 0 Then    ' Blank cell found in range
        Range("A1:A" & lr).SpecialCells(xlCellTypeBlanks).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False ' ERROR No cells found
    End If

That should handle 'no blank cells found'.

 

[Add365]

Did you also place 'On Error GoTo 0' after the line that errors? In other words:

On Error Resume Next
Line that errors
On Error GoTo 0

Yes I have placed On error resume next and on error goto 0 and it messes it up, its annoying becasue the code is working exactly the way I want it to but pops up with the run time error box