Object Variable Not Set

**max57** · 01-03-2010, 12:44 PM

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.

                      'Assigns the variable "rngCash" to the first non-empty cell in "B1"
                          With ThisWorkbook
                            .Sheets(4).Select

                           ‘Object variable or With block variable not set : Variant/Integer’
                           Set rngCash = DestSh.Range("B1").End(xlDown).Offset(1, 0)
                                rngCash.Select
                           
                           rngCash(0, 1).Value = Dcell.Value
                            rngCash(0, 2).Value = "Cash"
                            rngCash(0, 3).Value = "   ----------"
                            rngCash(0, 4).Value = Rcell.Value
                            rngCash(0, 5).Value = "   ---
                            rngCash(0, 6).Value = Abbrev
                            rngCash(0, 7).Value = "   ---"
                           
                         End With

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

Thanks

**davesexcel** · 01-03-2010, 01:04 PM

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

 Set rngCash = Worksheets("Yearly Totals").Range("B1").End(xlDown).Offset(1, 0) '

**max57** · 01-03-2010, 01:22 PM

Thanks Dave!

That fixed it.

Much obliged.

**max57** · 01-03-2010, 01:32 PM

Hello Leith,

I did omit the 'rngCash.select' upon your suggestion and it worked with Dave's suggestion.

Isn't the item method in this case referring to Rows?
In my case, I'm going across a row, hence the need for the offset-which I neglected in my code but have since added in.

Now my problem is just getting the Date cell to increment in the paste procedure.

I have to take a look at why this is not happening all of a sudden.

Thanks much Leith!

Mark

**max57** · 01-03-2010, 01:36 PM

Hi Dave,

I thought that if I declared it as a public variable, that it wouldn't have to be dimmed in each module.

I've since removed it from there and dimmed it in each module.

At this point, I'm just beginning to understand enough VBA to confuse myself.

Mark

**Leith Ross** · 01-03-2010, 02:10 PM

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.

**Leith Ross** · 01-03-2010, 01:08 PM

Hello ,

Here are two ways to fix the problem...

                     'Assigns the variable "rngCash" to the first non-empty cell in "B1"
                          With ThisWorkbook
                            .Sheets(4).Select

                           ‘Object variable or With block variable not set : Variant/Integer’
                           Set rngCash = DestSh.Range("B1").End(xlDown).Offset(1, 0)
                           
                            rngCash.Cells(1, 1).Value = Dcell.Value
                            rngCash.Cells(2, 1).Value = "Cash"
                            rngCash.Cells(3, 1).Value = "   ----------"
                            rngCash.Cells(4, 1).Value = Rcell.Value
                            rngCash.Cells(5, 1).Value = "   ---
                            rngCash.Cells(6, 1).Value = Abbrev
                            rngCash.Cells(7, 1).Value = "   ---"
                           
                         End With

  Or this method...

                     'Assigns the variable "rngCash" to the first non-empty cell in "B1"
                          With ThisWorkbook
                            .Sheets(4).Select

                           ‘Object variable or With block variable not set : Variant/Integer’
                           Set rngCash = DestSh.Range("B1").End(xlDown).Offset(1, 0)
          http://www.excelforum.com/images/editor/color.gif                 
                            rngCash.Item1).Value = Dcell.Value
                            rngCash.Item(2).Value = "Cash"
                            rngCash.Item(3).Value = "   ----------"
                            rngCash.Item(4).Value = Rcell.Value
                            rngCash.Item(5).Value = "   ---
                            rngCash.Item(6).Value = Abbrev
                            rngCash.Item(7).Value = "   ---"
                           
                         End With

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.

**davesexcel** · 01-03-2010, 01:24 PM

Similar problem in the other macro
change to

    CopyRng.Copy


                With DestSh.Range("B1").End(xlDown).Offset(1, 0)
                    .PasteSpecial xlPasteValues


                End With