For next string loop

  • Thread starter ragtopcaddy via AccessMonster.com
  • Start date
R

ragtopcaddy via AccessMonster.com

I am trying to produce what the following code would do, if only it would do
it. What I need is a suggestion as to how to accomplish it. I could always
use and array or a recordset, but was wondering if there isn't some kind of
loop I could use that would do it.

for sItem = "Sales", "Profit", "Expense", "GM"
(code here)
next sItem

Any suggestions would be appreciated.
 
A

Albert D. Kallal

In most Cases it's better to put the stuff in to a table, and not in the
code.

However, you can go:

Dim v As Variant
Dim i As Integer

v = Split("Sales,Profit,Expense,GM", ",")
For i = 0 To UBound(v)
Debug.Print v(i)
Next i

or, even perhaps more appropriate:

Dim v As Variant
Dim i As Integer

v = Array("Sales", "Profit", "Expense", "GM")
For i = 0 To UBound(v)
Debug.Print v(i)
Next i

However, my choice is usually a collection.

Dim c As New Collection
Dim v As Variant

c.Add "Sales"
c.Add "Profit"
c.Add "Expense"
c.Add "GM"

For Each v In c
Debug.Print v
Next
 
R

ragtopcaddy via AccessMonster.com

Albert,

Thank you for your great suggestions.

I will create the collection.

Thanks,
 
R

Robert Morley

Another variant of Albert's code would be:

Public Sub TestArray()
Dim vItem As Variant

For Each vItem In Array("Apples", "Oranges", "Pears")
Debug.Print vItem
Next
End Sub

As with Albert's code, if you specifically want vItem to be a String instead
of a Variant, you'll have to use CStr() or assign it to a String variable
within the loop; you can't declare vItem itself as a String.


Rob
 
R

ragtopcaddy via AccessMonster.com

Robert,

Thanks for the additional info.

Regards,


Robert said:
Another variant of Albert's code would be:

Public Sub TestArray()
Dim vItem As Variant

For Each vItem In Array("Apples", "Oranges", "Pears")
Debug.Print vItem
Next
End Sub

As with Albert's code, if you specifically want vItem to be a String instead
of a Variant, you'll have to use CStr() or assign it to a String variable
within the loop; you can't declare vItem itself as a String.

Rob
In most Cases it's better to put the stuff in to a table, and not in the
code.
Click to expand...
[quoted text clipped - 32 lines]
Debug.Print v
Next
Click to expand...
Click to expand...
 
Top