Excel 2007 : Why am I getting this 1004 error?

[ThomasHaller]

Hello folks,

Below is a block of code that is attempting to first find the last Row number of some imported data on a spreadsheet (A), then take a value entered by the user (on a different sheet B) and paste it into another column of the original spreadsheet (A). only pasting the same number of rows as was determined to be in the imported data.

The determination of the last Row number (LastRow) seems to work fine.

The pasting part of the code works just fine if I hard coded a value for the last Row number (like use 6725 instead of "LastRow").

But when I try to use my "LastRow" variable in the pasting code, I get an error:

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

And clicking the Debug button highlights the line (in yellow):

.Range(.Range("L4"), .Range("L" & "LastRow")).Value = Target.Value

Any idea why I am getting this error here?

Please Login or Register  to view this content.

[tigeravatar]

Remove the quotation marks from LastRow. You want the variable that LastRow represents, not the phrase "LastRow".

[ThomasHaller]

Hello tigeravatar,

If I remove the quotes, the data gets pasted starting at the "L4" cell and goes UP to row 1 (or "L1") instead of going down to LastRow (Dim'ed as Integer with an actual value of 6725). This leads me to believe there is some "bad" conversion going on when this LastRow integer is concatenated (&) to the Row identifier.

I have tried using a "STR" conversion, Dim'ing the LastRow as a String, but still no success!

- Thomas

[tigeravatar]

ThomasHaller,

Looks like your code can be reduced to the following. See if that accomplishes what you're looking for:

Please Login or Register  to view this content.

[ThomasHaller]

Thanks ta, I will check that out.

Also I think your earlier code correction was correct (so to speak) and my problem is the variable LastRow, which is properly calculated elsewhere (for the sample database I am using) as 6725, but in the local code shown here, it gets calculated as 1 (or 0). Which explains the upward paste.

So I have to figure out what happened to my LastRow calculation code which used to work...

- Thomas

[ThomasHaller]

Yeah! I found the problem, I guess I was not being "explicit" enough in the code that calculated the LastRow value. I was having trouble finding how to properly specify in the Sub procedure code, but I did have a Function version that worked fine. So my fix was to call the Function in the Sub procedure.

Here is the Function code:

Please Login or Register  to view this content.

Here is the Sub procedure code that works:

Please Login or Register  to view this content.

But now I have new problem! :D

- Thomas