Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Loop thru variables
I have 5 variables which I want to run macro for.
If it was nos, I would have done for i=1 to 5 loop but these are variables. How can I run a loop on this? Kindly suggest. Regards, Madiya |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Loop thru variables
On Oct 15, 12:11 pm, Madiya wrote:
I have 5 variables which I want to run macro for. If it was nos, I would have done for i=1 to 5 loop but these are variables. How can I run a loop on this? Kindly suggest. Regards, Madiya Could you please tell me the purpose of this program? What are you trying to attempt to do? Regards Neville. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Loop thru variables
On Oct 15, 12:24 pm, cablegui wrote:
On Oct 15, 12:11 pm, Madiya wrote: I have 5 variables which I want to run macro for. If it was nos, I would have done for i=1 to 5 loop but these are variables. How can I run a loop on this? Kindly suggest. Regards, Madiya Could you please tell me the purpose of this program? What are you trying to attempt to do? Regards Neville. Thanks for your reply. I am trying to creat report for different states from one of our master records workbook. This requires me to filter out the required state and copy filtered records to a new book. I have the entire program ready where I am manually entering the state code in a input box. This works fine. I want to eliminate input box and run the programm for selected states. Regards, Madiya. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Loop thru variables
Madiya:
Have you tried using an array: Sub test() Const sheetname As String = "statelist" ' name of the sheet ' where the list of states is Dim statelist(1 To 5) As String ' array to hold states Dim i As Integer, j As Integer ' counters i = 0 ' read the list Do While (ThisWorkbook.Sheets(sheetname).Cells(i + 1, 1) _ < "") i = i + 1 statelist(i) = ThisWorkbook.Sheets(sheetname).Cells(i, 1) Loop For j = 1 To i Step 1 ' process the list ' use step 1 in case of ' 0 items ' run report code using statelist(j) Next j End Sub End Sub '-------------end---------- You need a list of states on a work sheet in the book where the macro is called sheet 'statelist' There are other ways to do it but this is the most flexible, as you can dynamically change the states on the statelist. -- Hope this helps Martin Fishlock, Bangkok, Thailand Please do not forget to rate this reply. "Madiya" wrote: On Oct 15, 12:24 pm, cablegui wrote: On Oct 15, 12:11 pm, Madiya wrote: I have 5 variables which I want to run macro for. If it was nos, I would have done for i=1 to 5 loop but these are variables. How can I run a loop on this? Kindly suggest. Regards, Madiya Could you please tell me the purpose of this program? What are you trying to attempt to do? Regards Neville. Thanks for your reply. I am trying to creat report for different states from one of our master records workbook. This requires me to filter out the required state and copy filtered records to a new book. I have the entire program ready where I am manually entering the state code in a input box. This works fine. I want to eliminate input box and run the programm for selected states. Regards, Madiya. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Loop thru variables
Thanks for your help.
I do not have these list in any of the sheets. I would like to define the same in the code directly. I am sure there is a way to do the same. A little dought in the suggetion by Martin: What happens if variable defined is 1 to 5 and I have a list only upto 3? Also the 1st variable in the arrey starts from 0 or 1? Thanks. I really appreciate your help. Regards, Madiya. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Loop thru variables
As was mentioned previously, make an array.
Sub doarray() myarray = Array("one", "two", "etc") For Each Item In myarray MsgBox Item Next Item End Sub -- Don Guillett Microsoft MVP Excel SalesAid Software "Madiya" wrote in message ups.com... Thanks for your help. I do not have these list in any of the sheets. I would like to define the same in the code directly. I am sure there is a way to do the same. A little dought in the suggetion by Martin: What happens if variable defined is 1 to 5 and I have a list only upto 3? Also the 1st variable in the arrey starts from 0 or 1? Thanks. I really appreciate your help. Regards, Madiya. |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Loop thru variables
On Oct 16, 5:14 pm, "Don Guillett" wrote:
As was mentioned previously, make an array. Sub doarray() myarray = Array("one", "two", "etc") For Each Item In myarray MsgBox Item Next Item End Sub -- Don Guillett Microsoft MVP Excel SalesAid Software "Madiya" wrote in message ups.com... Thanks for your help. I do not have these list in any of the sheets. I would like to define the same in the code directly. I am sure there is a way to do the same. A little dought in the suggetion by Martin: What happens if variable defined is 1 to 5 and I have a list only upto 3? Also the 1st variable in the arrey starts from 0 or 1? Thanks. I really appreciate your help. Regards, Madiya.- Hide quoted text - - Show quoted text - Thank you Don. This is exactly what I want. This resolves my problem completly. Regards, Madiya |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Loop thru variables
|
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Setting Multiple Variables with a loop | Excel Discussion (Misc queries) | |||
evaluate and variables and loop... | Excel Programming | |||
Non Static Variables in a For...Next Loop | Excel Programming | |||
Is it possible to create variables in a loop.. | Excel Programming | |||
Write variables from a loop, once only | Excel Programming |