Object Variable Not Set

I’m sure this is a relatively simple fix, but I’m unable to find the answer.
This code is part of a routine that copies and pastes from “CASH RECEIPTS” sheet to “YEARLY TOTALS” sheet in the same workbook.
rngCash is assigned as a Range variable .
In the various fixes I’ve tried, the code worked to varying degrees. Now I’m confused.
Any help would be appreciated.

Please Login or Register  to view this content.

There is a workbook attached for reference
The Macro is “LoopHell”

Thanks

I never saw a Dim for the Worksheet, this may work though

Please Login or Register  to view this content.

Hello ,

Here are two ways to fix the problem...

Please Login or Register  to view this content.

In VBA it is rare to have to select an object perform performing an action on it. You will see I removed the rngCash.Selectstatements.

Similar problem in the other macro
change to

Please Login or Register  to view this content.

Hello Mark,

The Item property is a index into the array. The array is read from top left cell down to the bottom right cell. This why it works for either a single dimension array of cells for a row or column.

Hi Leith,

Following that train of thought, is it possible to loop through the entire CASH RECEIPTS page, copying all of the values into an array and then paste the entire array to the YEARLY TOTALS sheet? This would avoid having to go back and forth between the two pages.

I'm beginning to look at arrays as a method to handle such things but my knowledge is minimal at best.

Thanks
Mark

Hello Mark,

If you only need values to be copied then the quickest way is to use an array. The system performs a "block transfer" with arrays. That is. all the data is written at once and not a single element at a time. You can copy the range into a Variant variable without having to dimension the rows and columns to be used. This method only works with the Value of a range.

Please Login or Register  to view this content.

Hello Mark,

It sounds like the "CASH RECEIPTS" is a disparate (non contiguous) range of cells. You can loop through this range and then create a new range object of contiguous cells. It would help to see a sample of the data layout or your workbook.

Hello Mark,

Thanks for the workbook. I need to restart my computer because there seems to be a problem with file converter. I have Excel 2003 and downloaded the file converter for Office 2007. But as usual, there seems to be a glitch or two.

Hello Mark,

Thanks. I just got my system restarted and was able to convert the original file.

Hello Mark,

After looking at the file, I am not sure were you want the "Cash Receipts" to be copied to. Is it the "Yearly Totals" sheet?

Hello Mark,

Disregard that last post. I found what I was looking for after I ran the macro.

Hello Mark,

Here is the revised macro and it has been added to the attached workbook.

Please Login or Register  to view this content.

Hi Leith,

Thanks for your time.
Very interesting way of coding it.
I have to study this and work with it in order get to know it.

I ran the macro in the attached workbook and it worked fine. On a second run, it only pasted the dates.

I copy and pasted it into another copy, but in Excel 2007. It ran exactly the same.

I did finish my workbook and have attached for you perusal.

Leith, thanks for all your ideas and suggestions.
Quite a learning experience.

Ciao!
Mark

Hello Mark,

I found the problem. The first time I defined the destination range to be "B3:H3", but the next time around it returns only a single cell "B19" as the destination range. I changed one line and added another line to the macro to fix it. The changes are in blue.

Please Login or Register  to view this content.