Using variable names in a loop

  • Thread starter Thread starter NDBC
  • Start date Start date
N

NDBC

Thanks for your help with solving the userform controls naming when using a
loop. I also have 5 public variables (as date) called time1, time2, time3,
time4 and time5. They are used in the following code. They used to work fine
when i did it 5 separate times but in order to minimise code i tried,

For Box = 5 to 1 step -1

Cells(riderCell.Row, Columns.Count).End(xlToLeft).Offset(0, 1) =
WorksheetFunction.Text(("Time" & box), "[hh]:mm:ss")

I'm guessing I need some form of code to let it know that "time" & 5 is a
variable name not the text time5.

I am now starting to understand why I thought it was safer to have 5 times
as much code. I'm sure it's simple but can anybody help me.

Thanks
 
Hi "NDBC"

Sorry you cannot mention variables as string in code.

Now since you have started using more and more variables; it is time for you
to start using array variables. The below link will give an overview of
Arrays (if you are not familiar with the basics of arrays)

http://msdn.microsoft.com/en-us/library/2k7ayc03(VS.80).aspx

Try the below code..There is only one variable declared which can store all
5 values...Try and feedback

Dim arrTime(5) As Variant

'Store values....(just for the sake of this example)
arrTime(1) = Range("A1")
arrTime(2) = Range("A1")
arrTime(3) = Range("A1")
arrTime(4) = Range("A1")
arrTime(5) = Range("A1")

For box = 5 To 1 Step -1
Cells(riderCell.Row, Columns.Count).End(xlToLeft).Offset(0, 1) = _
WorksheetFunction.Text(arrTime(box), "[hh]:mm:ss")
Next


If this post helps click Yes
 
Jacob, I've missed your help on my last few posts. Good to have you back. I
don't make much of a pro programmer but I hope I'm improving. Definitely
learning anyway. I was annoyed with myself for not being able to work this
problem out but I'm glad I didn't struggle with it for too long as it
couldn't be done.

Thanks for the array tips. I now have

arrtime(1) = time1
arrtime(2) = time2 etc

along with the line you wrote below. Works a treat as does everything you've
helped me with.

I think will have to call my program. Pony Express Timing written by NDBC
and Jacob Skaria.

I would use your name but you probably wouldn't want to be associated with
some of my code.

Thanks

Knowing my skills, we'll being talking again soon.
Jacob Skaria said:
Hi "NDBC"

Sorry you cannot mention variables as string in code.

Now since you have started using more and more variables; it is time for you
to start using array variables. The below link will give an overview of
Arrays (if you are not familiar with the basics of arrays)

http://msdn.microsoft.com/en-us/library/2k7ayc03(VS.80).aspx

Try the below code..There is only one variable declared which can store all
5 values...Try and feedback

Dim arrTime(5) As Variant

'Store values....(just for the sake of this example)
arrTime(1) = Range("A1")
arrTime(2) = Range("A1")
arrTime(3) = Range("A1")
arrTime(4) = Range("A1")
arrTime(5) = Range("A1")

For box = 5 To 1 Step -1
Cells(riderCell.Row, Columns.Count).End(xlToLeft).Offset(0, 1) = _
WorksheetFunction.Text(arrTime(box), "[hh]:mm:ss")
Next


If this post helps click Yes
---------------
Jacob Skaria


NDBC said:
Thanks for your help with solving the userform controls naming when using a
loop. I also have 5 public variables (as date) called time1, time2, time3,
time4 and time5. They are used in the following code. They used to work fine
when i did it 5 separate times but in order to minimise code i tried,

For Box = 5 to 1 step -1

Cells(riderCell.Row, Columns.Count).End(xlToLeft).Offset(0, 1) =
WorksheetFunction.Text(("Time" & box), "[hh]:mm:ss")

I'm guessing I need some form of code to let it know that "time" & 5 is a
variable name not the text time5.

I am now starting to understand why I thought it was safer to have 5 times
as much code. I'm sure it's simple but can anybody help me.

Thanks
Click to expand...
Click to expand...
 
Thanks for your comemnts..I can see a ** big ** improvement in your postings.
More than happy that i could be of little help..

If this post helps click Yes
---------------
Jacob Skaria


NDBC said:
Jacob, I've missed your help on my last few posts. Good to have you back. I
don't make much of a pro programmer but I hope I'm improving. Definitely
learning anyway. I was annoyed with myself for not being able to work this
problem out but I'm glad I didn't struggle with it for too long as it
couldn't be done.

Thanks for the array tips. I now have

arrtime(1) = time1
arrtime(2) = time2 etc

along with the line you wrote below. Works a treat as does everything you've
helped me with.

I think will have to call my program. Pony Express Timing written by NDBC
and Jacob Skaria.

I would use your name but you probably wouldn't want to be associated with
some of my code.

Thanks

Knowing my skills, we'll being talking again soon.
Jacob Skaria said:
Hi "NDBC"

Sorry you cannot mention variables as string in code.

Now since you have started using more and more variables; it is time for you
to start using array variables. The below link will give an overview of
Arrays (if you are not familiar with the basics of arrays)

http://msdn.microsoft.com/en-us/library/2k7ayc03(VS.80).aspx

Try the below code..There is only one variable declared which can store all
5 values...Try and feedback

Dim arrTime(5) As Variant

'Store values....(just for the sake of this example)
arrTime(1) = Range("A1")
arrTime(2) = Range("A1")
arrTime(3) = Range("A1")
arrTime(4) = Range("A1")
arrTime(5) = Range("A1")

For box = 5 To 1 Step -1
Cells(riderCell.Row, Columns.Count).End(xlToLeft).Offset(0, 1) = _
WorksheetFunction.Text(arrTime(box), "[hh]:mm:ss")
Next


If this post helps click Yes
---------------
Jacob Skaria


NDBC said:
Thanks for your help with solving the userform controls naming when using a
loop. I also have 5 public variables (as date) called time1, time2, time3,
time4 and time5. They are used in the following code. They used to work fine
when i did it 5 separate times but in order to minimise code i tried,

For Box = 5 to 1 step -1

Cells(riderCell.Row, Columns.Count).End(xlToLeft).Offset(0, 1) =
WorksheetFunction.Text(("Time" & box), "[hh]:mm:ss")

I'm guessing I need some form of code to let it know that "time" & 5 is a
variable name not the text time5.

I am now starting to understand why I thought it was safer to have 5 times
as much code. I'm sure it's simple but can anybody help me.

Thanks
Click to expand...
Click to expand...
Click to expand...
 
Back
Top