Loop through a range of another sheet of the same workbook to change cell values

[flipjarg]

Alright, I've been playing with this for a while. Where am I going wrong? Basically, all I want to do is have this function to find the last row of copied data, and concatenate some cells and change the value of some cells.

I get this error when I run the code below...

Run-time error '1004': Method 'Range' of object '_Worksheet' failed

Please Login or Register  to view this content.

[LJMetzger]

Hi flipjarg,

1. I found problems in the following lines:

Please Login or Register  to view this content.

2. To prevent typos from ruining days and weeks of work 'Option Explicit' is NEEDED at the top of each code module. This prevents errors caused by missspellings and FORCES every variable to be DECLARED (e.g. dim i as Integer). http://www.cpearson.com/excel/DeclaringVariables.aspx

3. It probably doesn't make any difference in this case, but in Excel 2007 the number of lines can overflow the Integer type. Row numbers should be type 'Long'.

Please Login or Register  to view this content.

4. After the 'PasteSpecial' line, the clipboard buffer should probably be cleared.

Please Login or Register  to view this content.

Lewis

[flipjarg]

1. I found problems in the following lines:

Please Login or Register  to view this content.

Thanks for your reply and for pointing that out. That should have been more obvious to me ... I was working on other code that day as well and for some reason I was consistently doing this in all of my code that day. A good sign I had my head in VBA for too long in one sitting

2. To prevent typos from ruining days and weeks of work 'Option Explicit' is NEEDED at the top of each code module. This prevents errors caused by missspellings and FORCES every variable to be DECLARED (e.g. dim i as Integer). http://www.cpearson.com/excel/DeclaringVariables.aspx

3. It probably doesn't make any difference in this case, but in Excel 2007 the number of lines can overflow the Integer type. Row numbers should be type 'Long'.

Please Login or Register  to view this content.

4. After the 'PasteSpecial' line, the clipboard buffer should probably be cleared.

Please Login or Register  to view this content.

Thanks for these other tips. I'm learning VBA as I go, as needed. I am definitely going to start using these.

[LJMetzger]

I'm glad I could be of some help.

It's surprising how much smarter I get sometimes after a good night's sleep.