Hi,

I have a more comprehensive query below but don't really have the time to
wait for a comprehensive answer.

Could you please help me with going through a list in VBA?

I have a named range and the first column has a list of countries. I want to
go through this list and do an action for each country. Can you please help
with how to do this? I've tried things like application.index(myrange,,1)
with no luck, and don't know the appropriate way to cycle through once I have
that too.

I have a fuller query ('Creating multiple pivot report at run time') with no
replies below. If that is a step too far, help with the abive would be
greatly appreciated.

Many thanks,

Basil

Try something like the following:

Dim R As Range
For Each R In Range("MyRange").Columns(1).Cells
R.EntireRow.Cells(1,"F") = 1234
Next R

Cordially,
Chip Pearson
Microsoft MVP 1998 - 2010
Pearson Software Consulting, LLC
www.cpearson.com
[email on web site]


On Tue, 12 Jan 2010 16:35:01 -0800, Basil
<(E-Mail Removed)> wrote:

>Hi,
>
>I have a more comprehensive query below but don't really have the time to
>wait for a comprehensive answer.
>
>Could you please help me with going through a list in VBA?
>
>I have a named range and the first column has a list of countries. I want to
>go through this list and do an action for each country. Can you please help
>with how to do this? I've tried things like application.index(myrange,,1)
>with no luck, and don't know the appropriate way to cycle through once I have
>that too.
>
>I have a fuller query ('Creating multiple pivot report at run time') with no
>replies below. If that is a step too far, help with the abive would be
>greatly appreciated.
>
>Many thanks,
>
>Basil

Thanks Chip, I can't get it to work. It keeps throwing an error on the first
row (I've copied your exact code and also tried specifically referencing the
worksheet of the named range too... nothing.

I'll try and be more specific. I have a named range called 'Country_Data'
(25 columns wide, variable row height) on a sheet called 'Country Analysis'.
In column one is the country name.

I want to click a button and the code to go through the first column and
show me a msgbox for each country in that first column with the country name
displayed. Once all the countries have had a message box displayed, it will
stop.

I'm using Excel 2003.

Thanks!

Basil

"Chip Pearson" wrote:

>
>
> Try something like the following:
>
> Dim R As Range
> For Each R In Range("MyRange").Columns(1).Cells
> R.EntireRow.Cells(1,"F") = 1234
> Next R
>
> Cordially,
> Chip Pearson
> Microsoft MVP 1998 - 2010
> Pearson Software Consulting, LLC
> www.cpearson.com
> [email on web site]
>
>
> On Tue, 12 Jan 2010 16:35:01 -0800, Basil
> <(E-Mail Removed)> wrote:
>
> >Hi,
> >
> >I have a more comprehensive query below but don't really have the time to
> >wait for a comprehensive answer.
> >
> >Could you please help me with going through a list in VBA?
> >
> >I have a named range and the first column has a list of countries. I want to
> >go through this list and do an action for each country. Can you please help
> >with how to do this? I've tried things like application.index(myrange,,1)
> >with no luck, and don't know the appropriate way to cycle through once I have
> >that too.
> >
> >I have a fuller query ('Creating multiple pivot report at run time') with no
> >replies below. If that is a step too far, help with the abive would be
> >greatly appreciated.
> >
> >Many thanks,
> >
> >Basil
> .
>

The problem seems to be in Range("MyRange").

If I type in a reference like "A7:F34" it works fine, but not when
referencing the named range. I've tried adding Sheets("Country Analysis") or
Worksheets("Country Analysis") beforehand but it threw up a different error.

"Chip Pearson" wrote:

>
>
> Try something like the following:
>
> Dim R As Range
> For Each R In Range("MyRange").Columns(1).Cells
> R.EntireRow.Cells(1,"F") = 1234
> Next R
>
> Cordially,
> Chip Pearson
> Microsoft MVP 1998 - 2010
> Pearson Software Consulting, LLC
> www.cpearson.com
> [email on web site]
>
>
> On Tue, 12 Jan 2010 16:35:01 -0800, Basil
> <(E-Mail Removed)> wrote:
>
> >Hi,
> >
> >I have a more comprehensive query below but don't really have the time to
> >wait for a comprehensive answer.
> >
> >Could you please help me with going through a list in VBA?
> >
> >I have a named range and the first column has a list of countries. I want to
> >go through this list and do an action for each country. Can you please help
> >with how to do this? I've tried things like application.index(myrange,,1)
> >with no luck, and don't know the appropriate way to cycle through once I have
> >that too.
> >
> >I have a fuller query ('Creating multiple pivot report at run time') with no
> >replies below. If that is a step too far, help with the abive would be
> >greatly appreciated.
> >
> >Many thanks,
> >
> >Basil
> .
>

Found a workaround. The problem was that the reference to the range was not
working. I believe this is because it was a dynamic range (defined using the
offset formula). So I set a fixed range and dealt with blank cells through
the code.

"Basil" wrote:

> The problem seems to be in Range("MyRange").
>
> If I type in a reference like "A7:F34" it works fine, but not when
> referencing the named range. I've tried adding Sheets("Country Analysis") or
> Worksheets("Country Analysis") beforehand but it threw up a different error.
>
> "Chip Pearson" wrote:
>
> >
> >
> > Try something like the following:
> >
> > Dim R As Range
> > For Each R In Range("MyRange").Columns(1).Cells
> > R.EntireRow.Cells(1,"F") = 1234
> > Next R
> >
> > Cordially,
> > Chip Pearson
> > Microsoft MVP 1998 - 2010
> > Pearson Software Consulting, LLC
> > www.cpearson.com
> > [email on web site]
> >
> >
> > On Tue, 12 Jan 2010 16:35:01 -0800, Basil
> > <(E-Mail Removed)> wrote:
> >
> > >Hi,
> > >
> > >I have a more comprehensive query below but don't really have the time to
> > >wait for a comprehensive answer.
> > >
> > >Could you please help me with going through a list in VBA?
> > >
> > >I have a named range and the first column has a list of countries. I want to
> > >go through this list and do an action for each country. Can you please help
> > >with how to do this? I've tried things like application.index(myrange,,1)
> > >with no luck, and don't know the appropriate way to cycle through once I have
> > >that too.
> > >
> > >I have a fuller query ('Creating multiple pivot report at run time') with no
> > >replies below. If that is a step too far, help with the abive would be
> > >greatly appreciated.
> > >
> > >Many thanks,
> > >
> > >Basil
> > .
> >