I would like to scroll through a column of entries.
So I set up a range:

Dim av_range As Range
Set av_range = Worksheets(1).Range("A2").End(xlDown)

However when I get to the next line

For Each c In Range("av_range")

I get the following error:
Runtime error 1004
Application defined or object-defined error

What exactly am I doing wrong?

Since av_range is a range object, you can access it directly.
Change

For Each c In Range("av_range")
to
For Each c In av_range


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com



"David Gerstman" wrote in message
...
I would like to scroll through a column of entries.
So I set up a range:

Dim av_range As Range
Set av_range = Worksheets(1).Range("A2").End(xlDown)

However when I get to the next line

For Each c In Range("av_range")

I get the following error:
Runtime error 1004
Application defined or object-defined error

What exactly am I doing wrong?

Thanks so much Chip.
It's working now and my colleague (for whom I wrote the macro) is pleased.
David

"Chip Pearson" wrote:

Since av_range is a range object, you can access it directly.
Change

For Each c In Range("av_range")
to
For Each c In av_range


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com



"David Gerstman" wrote in message
...
I would like to scroll through a column of entries.
So I set up a range:

Dim av_range As Range
Set av_range = Worksheets(1).Range("A2").End(xlDown)

However when I get to the next line

For Each c In Range("av_range")

I get the following error:
Runtime error 1004
Application defined or object-defined error

What exactly am I doing wrong?

David,

Two things wrong, perhaps:

Set av_range = Worksheets(1).Range("A2").End(xlDown)

This is now a single cell, and you might actually mean:

Set av_range = Worksheets(1).Range("A2",
Worksheets(1).Range("A2").End(xlDown))

And this:

For Each c In Range("av_range")

Should actually be:

For Each c In av_range

HTH,
Bernie
MS Excel MVP


"David Gerstman" wrote in message
...
I would like to scroll through a column of entries.
So I set up a range:

Dim av_range As Range
Set av_range = Worksheets(1).Range("A2").End(xlDown)

However when I get to the next line

For Each c In Range("av_range")

I get the following error:
Runtime error 1004
Application defined or object-defined error

What exactly am I doing wrong?

Bernie,
Thanks for catching both mistakes and helping me complete the task for my
colleague.
I learned something new and valuable from this exercise and I appreciate
your help in helping it happen.
David

"Bernie Deitrick" wrote:

David,

Two things wrong, perhaps:

Set av_range = Worksheets(1).Range("A2").End(xlDown)

This is now a single cell, and you might actually mean:

Set av_range = Worksheets(1).Range("A2",
Worksheets(1).Range("A2").End(xlDown))

And this:

For Each c In Range("av_range")

Should actually be:

For Each c In av_range

HTH,
Bernie
MS Excel MVP


"David Gerstman" wrote in message
...
I would like to scroll through a column of entries.
So I set up a range:

Dim av_range As Range
Set av_range = Worksheets(1).Range("A2").End(xlDown)

However when I get to the next line

For Each c In Range("av_range")

I get the following error:
Runtime error 1004
Application defined or object-defined error

What exactly am I doing wrong?