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

 

[Add365]

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'.

Brilliant yes this has worked, no errors anymore. Thanks so much for your help

 

[johnnyL]

Your welcome @Add365!

Now shorten up your bloated code a bit.

 

[Add365]

Your welcome @Add365!

Now shorten up your bloated code a bit.

Yes that's my next thing to do just wanted to get it all working first. Thanks