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]

Hi and welcome to MrExcel.

Try this:

Sub test()
  Dim i As Long
  With Sheets("Stock Out")
    i = .Cells(Evaluate(Replace("MIN(IF('" & .Name & "'!A1:A#="""",ROW(A1:A#)))", "#", .Cells(Rows.Count, "A").End(xlUp).Row + 1)), "A").Row
    .Range("A" & i).Value = Sheets("Invoice").Range("C6").Value
  End With
End Sub

 

[johnnyL]

@Add365
Your current code is pasting the value found in C6 from the 'Invoice' Sheet over to all blank cells found in the A column of 'Stock Out' sheet down to the last used row in that A column. That doesn't match what your intention stated.

Please restate what your intentions are, maybe post a before and after desire.

Here is your original code shortened that does the same thing:

    Dim lr As Long
'
    lr = Sheets("Stock Out").Range("A" & Rows.Count).End(xlUp).Row
'
    Sheets("Invoice").Range("C6").Copy
    Sheets("Stock Out").Range("A1:A" & lr).SpecialCells(xlCellTypeBlanks).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
'
    Application.CutCopyMode = False

@DanteAmor Gave you some code that pastes the value found in C6 from the 'Invoice' Sheet to one blank cell found in the A column of 'Stock Out' sheet down to the last used row in that A column. But I don't think that is what your intention was either.

 

[DanteAmor]

The code must put the value in an empty cell, but if there is no empty cell then it sends an error.
What my code does is put the value C6 in some empty cell and if there is no empty cell then it puts the value in the next available row.

 

[Add365]

@Add365
Your current code is pasting the value found in C6 from the 'Invoice' Sheet over to all blank cells found in the A column of 'Stock Out' sheet down to the last used row in that A column. That doesn't match what your intention stated.

Please restate what your intentions are, maybe post a before and after desire.

Here is your original code shortened that does the same thing:

    Dim lr As Long
'
    lr = Sheets("Stock Out").Range("A" & Rows.Count).End(xlUp).Row
'
    Sheets("Invoice").Range("C6").Copy
    Sheets("Stock Out").Range("A1:A" & lr).SpecialCells(xlCellTypeBlanks).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
'
    Application.CutCopyMode = False

@DanteAmor Gave you some code that pastes the value found in C6 from the 'Invoice' Sheet to one blank cell found in the A column of 'Stock Out' sheet down to the last used row in that A column. But I don't think that is what your intention was either.

Hi,

So want Im trying to achieve is evertime I create an invoice I click my macro button which takes all the information from the invoice i.e who I sold the product to and what they ordered and what date etc and pastes it into the stock out sheet. Obviously there will be new info added to the stock out sheet all the time so the info is being added to the next avaialbe cell below.

The issue Im having is cell C6 is the company I have sold the goods to so say I sell 10 items that's 10 rows of information that pastes in fine but cell C6 pastes in the first available next cell leaving 9 blank cells underneath that need to be filled with the same comapny name so there is no gaps in the data and the filter system works better. What I have done is pasted in the first available cell then gone to the last piece of data on column B and moved across one to paste into that cell with C6's data, then I can fill in all the blank cells in between with the data below. Not sure if this is the best way to do it but it has been working but just keep gettting the no cells were found error if I only paste in 2 rows of data, obvioulsy because there are no blank cells but thought there would be a way round this.

Let me know what you think.

Thanks

 

[johnnyL]

@Add365 Can you post a before and after example of what you want the desired output to look like

 

[Add365]

@Add365 Can you post a before and after example of what you want the desired output to look like

Hi,

So below the 1st screenshot is the invoice I start with and that info transfers over to the "Stock out" sheet screenshot 2, as you can see there is gaps in the "Sold to" column on the 2nd screen shot as all the info is being transfered over fine to columns B to L on the "Stock Out" sheet but I am having to fill in the blanks in column A as I am copying cell C6 on the invoice and pasting it in the next available Cell on the "Stock Out" Sheet in Column A. I used the macro to do this which I listed earlier in this post but it only works if I transfer over more than 2 items, as if there is only 2 items then there are no blanks to fill in.

I have tried the On Error Resume Next & On Error GoTo 0 but then the data doesnt transfer over properly as it just skips that part of the macro.

Hope this all makes sense, Let me know what you think.

Thanks

 

[DanteAmor]

all the info is being transfered over fine to columns B to L on the "Stock Out" sheet

Do you have a code to do that? can you share it with us?

Did you try my code from post #2 to find the empty row?

 

[Add365]

Do you have a code to do that? can you share it with us?

Did you try my code from post #2 to find the empty row?

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.

Below is my code for it all (dont laugh too much as I wrote some of it and used the macro recorder for a lot of it lol)

Sub Stockcontrol()

Sheets("Stock Out").Range("A" & Rows.Count).End(xlUp).Offset(1).Value = Sheets("Invoice").Range("C6").Value
Sheets("Stock Out").Range("B" & Rows.Count).End(xlUp).Offset(1).Value = Sheets("Invoice").Range("F6").Value

Sheets("Invoice").Select
Range("A9").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Sheets("Stock Out").Select
Range("C" & Rows.Count).End(xlUp).Offset(1).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

Sheets("Invoice").Select
Range("C9").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Sheets("Stock Out").Select
Range("D" & Rows.Count).End(xlUp).Offset(1).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

Sheets("Invoice").Select
Range("B9").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Sheets("Stock Out").Select
Range("E" & Rows.Count).End(xlUp).Offset(1).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

Sheets("Invoice").Select
Range("D9").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Sheets("Stock Out").Select
Range("G" & Rows.Count).End(xlUp).Offset(1).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

Sheets("Invoice").Select
Range("F9").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Sheets("Stock Out").Select
Range("H" & Rows.Count).End(xlUp).Offset(1).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

Sheets("Delivery Note").Select
Set rng = Range("F14:F114")
rng.SpecialCells(xlCellTypeConstants).Select
Selection.Copy
Sheets("Stock Out").Select
Range("F" & Rows.Count).End(xlUp).Offset(1).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

Sheets("Stock Out").Select
Range("H2").Select
Selection.End(xlDown).Offset(0, 1).Select
Range(Selection, Selection.End(xlUp)).Select
Application.CutCopyMode = False
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

Sheets("Stock Out").Select
Range("H2").Select
Selection.End(xlDown).Offset(0, 2).Select
Range(Selection, Selection.End(xlUp)).Select
Application.CutCopyMode = False
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

Sheets("Stock Out").Select
Range("C2").Select
Selection.End(xlDown).Offset(0, -2).Value = Sheets("Invoice").Range("C6").Value

Sheets("Stock Out").Select
Range("C2").Select
Selection.End(xlDown).Offset(0, -1).Value = Sheets("Invoice").Range("F6").Value

Sheets("Customer List").Select
Range("F3").Select
Selection.Copy
Sheets("Stock Out").Select
Range("K" & Rows.Count).End(xlUp).Offset(1).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

Sheets("Stock Out").Select
Range("H2").Select
Selection.End(xlDown).Offset(0, 3).Value = Sheets("Customer List").Range("F3").Value

Sheets("Stock Out").Select
Range("C2").Select
Selection.End(xlDown).Offset(0, -2).Select
Range(Selection, Selection.End(xlUp)).Select
Application.CutCopyMode = False
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

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

Sheets("Invoice").Select
Range("F6").Select
Selection.Copy
Sheets("Stock Out").Select
lr = Range("B" & Rows.Count).End(xlUp).Row

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

Sheets("Customer List").Select
Range("F3").Select
Selection.Copy
Sheets("Stock Out").Select
lr = Range("K" & Rows.Count).End(xlUp).Row

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

End If
End Sub

 

[johnnyL]

@Add365 have you tested that code you just posted?