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(c ell_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_Co unt)

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"

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(c ell_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


"baconcow" wrote:

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(c ell_count))

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

Thanks

On 26 Maj, 20:22, baconcow wrote:
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

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.

"Jim Cone" wrote:

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_Co unt)

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"

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(c ell_count))

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

Thanks

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

"Mike H" wrote:

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


"baconcow" wrote:

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(c ell_count))

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

Thanks