Run-time Error '1004': Application-defined or object-defined error

Dear Gurus,

Run-time Error '1004': Application-defined or object-defined error

The above-mentioned error keeps popping up when I execute the code below. It was running perfectly fine till I switched the sourceWB workbook and made changes to the code to reflect the change.

When the error occurs, the following code was highlighted

lRow = ws1.Cells(Rows.Count, "B").End(xlUp).Row - 2

Since the change, i could not get the macro to run and it is driving me crazy.
Hope someone can help to highlight what could be the possible cause of this error.


Thanks in advance.

Please Login or Register  to view this content.

lRow = ws1.Cells(Rows.Count, "B").End(xlUp).Row - 2
It is highly likely that column B in sheet sourceWB.Sheets("Sheet1") is blank. Since the default value for IRow is 1 and 1 minus 2 is minus 1, that is, IRow is -1. You can't have a minus row.

Please Login or Register  to view this content.

Hi FDibbins,
First of all, I apologise for "cross-posting", I am not aware of it's against forum rules.
Very desperate to find a solution to this error asap.


To mehmetcik,
sourceWB "FRBO (Private Apt).xls" exist, and "Sheet1" as well in the workbook.
It was working fine when sourceWB was another file "Res Source WB.xls", i need to make a switch because it's an updated copy. But after changing it the error pops up.


To AB33
Thanks for highlighting the probable cause. sourceWB.Sheets("Sheet1") is filled with data (more than 100 rows of data).


To jindon
"lRow = ws1.Cells(ws1.Rows.Count, "B").End(xlUp).Row - 2"
I tried this as well, to specify where the source is so that there is no ambiguity. But it does not work.

Originally Posted by FDibbins

Please provide the link here, to where you x-posted, and share this link with them too.

https://www.mrexcel.com/forum/excel-...ned-error.html

My poor guess is then you are working on two different workbooks.

Set sourceWB = Workbooks("FRBO (Private Apt).xls")
Set targetWB = Workbooks("ck's database_Residential (updating 01 May 2018).xlsm")
If the active workbook is not sourceWB when you run the code, you will get an error as the code is unable to find it.

Originally Posted by whoosh

To jindon
"lRow = ws1.Cells(ws1.Rows.Count, "B").End(xlUp).Row - 2"
I tried this as well, to specify where the source is so that there is no ambiguity. But it does not work.

How is it not working?
Not working as you expect?

You said, you are getting "Run-time Error '1004': Application-defined or object-defined error".
Rows.Count is referring to the ACTVE workbook.
When .xlsm is active,and try to get the Rows.Count of the version before 2007 .xls, it raise the error because of the version difference.

Yes, I am working on 2 different workbooks. Both are opened when i execute the code.

I saved the sourceWB as macro-enabled format - FRBO (Private Apt).xls to FRBO (Private Apt).xlsm, when i run the code no error but nothing copied!

That's a different problem from what you asked.

Originally Posted by jindon

That's a different problem from what you asked.

Hi jindon,

I am trying various options to see if i can get it to work/ to find the cause to the problem.
The latest development is as stated in my previous post, saving xls to xlsm format, no errors but nothing copied as well.
Saving to xlsm format is not viable in the long run, because I'm getting the source in xls format for updating.

Then you need to clearly state what your actual problem is.

No body can see inside your brain.