Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Cycle through a list
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Cycle through a list
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 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Cycle through a list
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 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 . |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Cycle through a list
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 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 . |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Cycle through a list
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 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 . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
cycle through list and put data found in col a into col d | Excel Discussion (Misc queries) | |||
How do I keep result from 1 iteration cycle to use in next cycle? | Excel Discussion (Misc queries) | |||
Change/cycle cell entry to next option from validation list (macro | Excel Programming | |||
Cycle through a list (Part 2) | Excel Programming | |||
Cycle trhough a list of names | Excel Programming |