Loop thru variables

[Madiya]

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

 

[cablegui]

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.

 

[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.

 

[Guest]

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.

 

[Don Guillett]

If you have a simple list of the states

for each c in range("a2:a6")
run your code
next c

 

[Madiya]

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.

 

[Don Guillett]

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

 

[Madiya]

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








- Show quoted text -

Thank you Don.
This is exactly what I want.
This resolves my problem completly.

Regards,
Madiya

 

[Don Guillett]

Glad to help