Excel Run-Time Error 1004 No cells were found

Add365

New Member
Joined
Jun 12, 2021
Messages
11
Office Version
  1. 365
Platform
  1. Windows
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
 

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
13,749
Office Version
  1. 2007
Platform
  1. Windows
Hi and welcome to MrExcel.

Try this:

VBA Code:
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

Well-known Member
Joined
Nov 7, 2011
Messages
1,427
Office Version
  1. 2013
  2. 2007
Platform
  1. Windows
@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:

VBA Code:
    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

Well-known Member
Joined
Dec 3, 2018
Messages
13,749
Office Version
  1. 2007
Platform
  1. Windows
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

New Member
Joined
Jun 12, 2021
Messages
11
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

@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:

VBA Code:
    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

Well-known Member
Joined
Nov 7, 2011
Messages
1,427
Office Version
  1. 2013
  2. 2007
Platform
  1. Windows
@Add365 Can you post a before and after example of what you want the desired output to look like
 

Add365

New Member
Joined
Jun 12, 2021
Messages
11
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

@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

1624265767657.png


1624265807764.png
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
13,749
Office Version
  1. 2007
Platform
  1. Windows
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

New Member
Joined
Jun 12, 2021
Messages
11
Office Version
  1. 365
Platform
  1. Windows
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
 

Forum statistics

Threads
1,143,675
Messages
5,720,237
Members
422,271
Latest member
xbaca

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top