Sequential looping

[FISH]

Group,

I am stuck on a problem in looping. Im trying to make it
so that I do not have to write 5 procedures for the 5
different Variables. But im getting an error with my
integer changing and the macro recognizing it as a
variable.

Thanks in advance


Fish

'''''Code

Dim x as integer
Dim Med1 As String, Med2 As String, Med3 As String, Med4
As String, Med5 As String


Med2 = "Advil"
Med3 = "Aleve"
Med4 = "Aspirin"
Med5 = "NyQuil"
Med6 = "Day Quil"

x = 1

Do
Med = Med1 + x
ActiveCell.Offset(0, -1).Value = Med
ActiveCell.Offset(1, 0).Select
x = x + 1
Loop Until ActiveCell.Offset(-1, -1).Value = Med6

 

[Guest]

Hi
use an array. something like
Dim amed(0 to 5)

and loop through this array

 

[Jim Rech]

You cannot construct a variable name so, like Frank said, try an array. For
example:

Sub a()
Dim Counter As Integer
Dim MyArray As Variant
MyArray = Array("a", "b", "c", "d", "e")
For Counter = 0 To UBound(MyArray)
ActiveCell.Offset(Counter).Value = MyArray(Counter)
Next
End Sub

--
Jim Rech
Excel MVP
| Group,
|
| I am stuck on a problem in looping. Im trying to make it
| so that I do not have to write 5 procedures for the 5
| different Variables. But im getting an error with my
| integer changing and the macro recognizing it as a
| variable.
|
| Thanks in advance
|
|
| Fish
|
| '''''Code
|
| Dim x as integer
| Dim Med1 As String, Med2 As String, Med3 As String, Med4
| As String, Med5 As String
|
|
| Med2 = "Advil"
| Med3 = "Aleve"
| Med4 = "Aspirin"
| Med5 = "NyQuil"
| Med6 = "Day Quil"
|
| x = 1
|
| Do
| Med = Med1 + x
| ActiveCell.Offset(0, -1).Value = Med
| ActiveCell.Offset(1, 0).Select
| x = x + 1
| Loop Until ActiveCell.Offset(-1, -1).Value = Med6
|

 

[Ed]

Two things off the top of my head (and it doesn't often go much deeper):
1) Your Dims are Med1 through Med5, but your assigns are Med2 through Med6.
2) I don't think you can add the integer "x" to the last character of the
string name "Med#" to increment to the next string.

You would probably be better off using Select Case.

Ed

 

[Guest]

Thanks a bunch Frank

-----Original Message-----
Hi
use an array. something like
Dim amed(0 to 5)

and loop through this array


.