Problem Setting Range

[baconcow]

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

 

[baconcow]

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

 

[Jim Cone]

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.
--
Jim Cone
Portland, Oregon USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)



"baconcow"
<[email protected]>
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

 

[Mike H]

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

 

[Per Jessen]

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

Hi

Try this

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

Regards,
Per

 

[baconcow]

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.

 

[baconcow]

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