Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sequential looping
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sequential looping
Hi
use an array. something like Dim amed(0 to 5) and loop through this array "FISH" wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sequential looping
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 "FISH" wrote in message ... | 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 | |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sequential looping
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 "FISH" wrote in message ... 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sequential looping
Thanks a bunch Frank -----Original Message----- Hi use an array. something like Dim amed(0 to 5) and loop through this array "FISH" wrote: 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 . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sequential Numbers | Excel Discussion (Misc queries) | |||
sequential numbers | Excel Worksheet Functions | |||
sequential numbers | Excel Discussion (Misc queries) | |||
Non-sequential VLOOKUP function -OR- sequential sort of web query | Excel Worksheet Functions | |||
Sequential names on Sequential pages | Excel Worksheet Functions |