Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using variable names in a loop
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using variable names in a loop
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/libr...03(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" wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using variable names in a loop
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" wrote: 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/libr...03(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" wrote: 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using variable names in a loop
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" wrote: 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" wrote: 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/libr...03(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" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Loop with variable name? | Excel Discussion (Misc queries) | |||
For Each ... Next loop - need to reference the loop variable | Excel Programming | |||
Loop with variable | Excel Programming | |||
Loop through Worksheet Names | Excel Programming | |||
Loop using cell names | Excel Programming |