Problem Setting Range

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

baconcow - 26 May 2008 19:00 GMT

I have set ranges many times. Usually I set the range for more than one cell.
In this situation, I just want to use one cell.

If this code works:
Set count_range = Worksheets("Inventory").Range("L4")

How come this code doesn't work?
Set count_range =
Worksheets("Inventory").Range(count_range.Offset(cell_count))

Everything is defined properly, but I get an "Application-defined or
object-defined error".

Thanks

Reply to this Message

baconcow - 26 May 2008 19:22 GMT

Here is more code. I believe it has something to do with the object variables
inside it.

'(in general)

Dim cell_count As Long
Dim count_range As Range

Private Sub cell_setup()

cell_count = 0
Set count_range = Worksheets("Inventory").Range("C8")
While count_range.Borders(xlEdgeBottom).LineStyle = xlNone
cell_count = cell_count + 1
Set count_range = Worksheets("Inventory").Range("C8",
count_range.Offset(cell_count, 0))
Wend

Reply to this Message

Per Jessen - 26 May 2008 19:33 GMT

> Here is more code. I believe it has something to do with the object variables
> inside it.
[quoted text clipped - 13 lines]
> count_range.Offset(cell_count, 0))
> Wend

Hi

Try this

Set count_range = Worksheets("Inventory").Range("C8",
Range("C8").Offset(cell_count, 0))

Regards,
Per

Reply to this Message

Jim Cone - 26 May 2008 19:22 GMT

You need both lines here...
Set count_Range = Worksheets("Inventory").Range("L4")
Set count_Range = count_Range.Offset(cell_Count)

Or only one line here...
Set count_Range = Worksheets("Inventory").Range("L4").Offset(cell_Count)

And...
cell_Count must return a numeric value.

Signature

Jim Cone
Portland, Oregon USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)

"baconcow"
<baconcow@discussions.microsoft.com>
wrote in message
I have set ranges many times. Usually I set the range for more than one cell.
In this situation, I just want to use one cell.

If this code works:
Set count_range = Worksheets("Inventory").Range("L4")

How come this code doesn't work?
Set count_range =
Worksheets("Inventory").Range(count_range.Offset(cell_count))

Everything is defined properly, but I get an "Application-defined or
object-defined error".

Thanks

Reply to this Message

baconcow - 26 May 2008 19:38 GMT

That was my problem. Every time I had it updating within the While loop, I
only had the one line. The other line was outside. When I put it the other
way, it worked fine. I didn't know, however, that you could refer to it
without the Worksheet addition. Helpful. Thanks a lot.

> You need both lines here...
> Set count_Range = Worksheets("Inventory").Range("L4")
[quoted text clipped - 5 lines]
> And...
> cell_Count must return a numeric value.

Reply to this Message

Mike H - 26 May 2008 19:33 GMT

Hi

and when setting the offset ROW is assumed unless you incllude a comma to
indicate column

Set count_range = count_range.Offset(, cell_Count)

or you could include value for both row and column

Set count_range = count_range.Offset(10, cell_Count)

Mike

> I have set ranges many times. Usually I set the range for more than one cell.
> In this situation, I just want to use one cell.
[quoted text clipped - 10 lines]
>
> Thanks

Reply to this Message

baconcow - 26 May 2008 19:52 GMT

Yeah, I had issues with that already. I am only worrying about the rows this
time. Thanks!

> Hi
>
[quoted text clipped - 23 lines]
> >
> > Thanks

Reply to this Message