Problem Setting Range

  • Thread starter Thread starter baconcow
  • Start date Start date
B

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
 
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
 
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
 
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
 
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
Click to expand...

Hi

Try this

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

Regards,
Per
 
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.
 
Yeah, I had issues with that already. I am only worrying about the rows this
time. Thanks!
 
Back
Top