ExcelBanter - Loop thru variables

ExcelBanter (https://www.excelbanter.com/)

- Excel Programming (https://www.excelbanter.com/excel-programming/)

- - Loop thru variables (https://www.excelbanter.com/excel-programming/399319-loop-thru-variables.html)

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

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.

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.

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.

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.

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

Loop thru variables