| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need loop guidance
Hello, I need assistance with creating code for a loop (which i'm not good at
at all). I have this code below that I'm only showing a portion of. I want to turn this into a For Loop code because I have to do the below code from "SP1", "Total1" through "SP30" and "Total30" so I'm looking at over 60 lines of code. How can I trun this into a For i = 1 to 30 type code? Any help is appreciated. Code: SP1.Value = Sheets("data").Range("Line1SP").Value Total1.Value = Sheets("data").Range("Line1tot").Value SP2.Value = Sheets("data").Range("Line2SP").Value Total2.Value = Sheets("data").Range("Line2tot").Value SP3.Value = Sheets("data").Range("Line3SP").Value Total3.Value = Sheets("data").Range("Line3tot").Value SP4.Value = Sheets("data").Range("Line4SP").Value Total4.Value = Sheets("data").Range("Line4tot").Value |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Need loop guidance
In this sort of case, you can setup an array variable instead of setting up
individual varable names. Dim arrlngSP(30) As Long, arrlngTotal(30) As Long, I as Long For I = 1 to 30 Step 1 arrlngSP(I) = Worksheets("data").Range("Line" & CStr(I) & "SP").Value arrlngTotal(I) = Worksheets("data").Range("Line" & CStr(I) & "tot").Value Next I In the above example, the " Step 1" isn't necessary as the default is stepping one at a time going forward, but my general practice, I still like to keep things about as explicit as one can be given issues I ran into in the past with being implicit, not to mention changes that takes place from one version to the next such as from VB6 to VB.NET. Also, if your data type isn't long integer, then be sure to adjust your data type and variable name appropriately as needed. "Brad" wrote in message ... Hello, I need assistance with creating code for a loop (which i'm not good at at all). I have this code below that I'm only showing a portion of. I want to turn this into a For Loop code because I have to do the below code from "SP1", "Total1" through "SP30" and "Total30" so I'm looking at over 60 lines of code. How can I trun this into a For i = 1 to 30 type code? Any help is appreciated. Code: SP1.Value = Sheets("data").Range("Line1SP").Value Total1.Value = Sheets("data").Range("Line1tot").Value SP2.Value = Sheets("data").Range("Line2SP").Value Total2.Value = Sheets("data").Range("Line2tot").Value SP3.Value = Sheets("data").Range("Line3SP").Value Total3.Value = Sheets("data").Range("Line3tot").Value SP4.Value = Sheets("data").Range("Line4SP").Value Total4.Value = Sheets("data").Range("Line4tot").Value |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Need loop guidance
Perfect, thank you!
"Ronald R. Dodge, Jr." wrote: In this sort of case, you can setup an array variable instead of setting up individual varable names. Dim arrlngSP(30) As Long, arrlngTotal(30) As Long, I as Long For I = 1 to 30 Step 1 arrlngSP(I) = Worksheets("data").Range("Line" & CStr(I) & "SP").Value arrlngTotal(I) = Worksheets("data").Range("Line" & CStr(I) & "tot").Value Next I In the above example, the " Step 1" isn't necessary as the default is stepping one at a time going forward, but my general practice, I still like to keep things about as explicit as one can be given issues I ran into in the past with being implicit, not to mention changes that takes place from one version to the next such as from VB6 to VB.NET. Also, if your data type isn't long integer, then be sure to adjust your data type and variable name appropriately as needed. "Brad" wrote in message ... Hello, I need assistance with creating code for a loop (which i'm not good at at all). I have this code below that I'm only showing a portion of. I want to turn this into a For Loop code because I have to do the below code from "SP1", "Total1" through "SP30" and "Total30" so I'm looking at over 60 lines of code. How can I trun this into a For i = 1 to 30 type code? Any help is appreciated. Code: SP1.Value = Sheets("data").Range("Line1SP").Value Total1.Value = Sheets("data").Range("Line1tot").Value SP2.Value = Sheets("data").Range("Line2SP").Value Total2.Value = Sheets("data").Range("Line2tot").Value SP3.Value = Sheets("data").Range("Line3SP").Value Total3.Value = Sheets("data").Range("Line3tot").Value SP4.Value = Sheets("data").Range("Line4SP").Value Total4.Value = Sheets("data").Range("Line4tot").Value |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Need loop guidance
Actually, I just tried the code and I'm getting a runtime error '13': Type
Mismatch message. Any ideas? Thanks, "Ronald R. Dodge, Jr." wrote: In this sort of case, you can setup an array variable instead of setting up individual varable names. Dim arrlngSP(30) As Long, arrlngTotal(30) As Long, I as Long For I = 1 to 30 Step 1 arrlngSP(I) = Worksheets("data").Range("Line" & CStr(I) & "SP").Value arrlngTotal(I) = Worksheets("data").Range("Line" & CStr(I) & "tot").Value Next I In the above example, the " Step 1" isn't necessary as the default is stepping one at a time going forward, but my general practice, I still like to keep things about as explicit as one can be given issues I ran into in the past with being implicit, not to mention changes that takes place from one version to the next such as from VB6 to VB.NET. Also, if your data type isn't long integer, then be sure to adjust your data type and variable name appropriately as needed. "Brad" wrote in message ... Hello, I need assistance with creating code for a loop (which i'm not good at at all). I have this code below that I'm only showing a portion of. I want to turn this into a For Loop code because I have to do the below code from "SP1", "Total1" through "SP30" and "Total30" so I'm looking at over 60 lines of code. How can I trun this into a For i = 1 to 30 type code? Any help is appreciated. Code: SP1.Value = Sheets("data").Range("Line1SP").Value Total1.Value = Sheets("data").Range("Line1tot").Value SP2.Value = Sheets("data").Range("Line2SP").Value Total2.Value = Sheets("data").Range("Line2tot").Value SP3.Value = Sheets("data").Range("Line3SP").Value Total3.Value = Sheets("data").Range("Line3tot").Value SP4.Value = Sheets("data").Range("Line4SP").Value Total4.Value = Sheets("data").Range("Line4tot").Value |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Need loop guidance
Probably should Dim the SP(30) and Total(30) arrays as either String or
Variant. I am assuming that they are named ranges. "I" can remain Dim as Long. "Brad" wrote: Actually, I just tried the code and I'm getting a runtime error '13': Type Mismatch message. Any ideas? Thanks, "Ronald R. Dodge, Jr." wrote: In this sort of case, you can setup an array variable instead of setting up individual varable names. Dim arrlngSP(30) As Long, arrlngTotal(30) As Long, I as Long For I = 1 to 30 Step 1 arrlngSP(I) = Worksheets("data").Range("Line" & CStr(I) & "SP").Value arrlngTotal(I) = Worksheets("data").Range("Line" & CStr(I) & "tot").Value Next I In the above example, the " Step 1" isn't necessary as the default is stepping one at a time going forward, but my general practice, I still like to keep things about as explicit as one can be given issues I ran into in the past with being implicit, not to mention changes that takes place from one version to the next such as from VB6 to VB.NET. Also, if your data type isn't long integer, then be sure to adjust your data type and variable name appropriately as needed. "Brad" wrote in message ... Hello, I need assistance with creating code for a loop (which i'm not good at at all). I have this code below that I'm only showing a portion of. I want to turn this into a For Loop code because I have to do the below code from "SP1", "Total1" through "SP30" and "Total30" so I'm looking at over 60 lines of code. How can I trun this into a For i = 1 to 30 type code? Any help is appreciated. Code: SP1.Value = Sheets("data").Range("Line1SP").Value Total1.Value = Sheets("data").Range("Line1tot").Value SP2.Value = Sheets("data").Range("Line2SP").Value Total2.Value = Sheets("data").Range("Line2tot").Value SP3.Value = Sheets("data").Range("Line3SP").Value Total3.Value = Sheets("data").Range("Line3tot").Value SP4.Value = Sheets("data").Range("Line4SP").Value Total4.Value = Sheets("data").Range("Line4tot").Value |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Need loop guidance
Is your data long integer or some other data type such as string?
Sincerely, Ronald R. Dodge, Jr. "Brad" wrote in message ... Actually, I just tried the code and I'm getting a runtime error '13': Type Mismatch message. Any ideas? Thanks, "Ronald R. Dodge, Jr." wrote: In this sort of case, you can setup an array variable instead of setting up individual varable names. Dim arrlngSP(30) As Long, arrlngTotal(30) As Long, I as Long For I = 1 to 30 Step 1 arrlngSP(I) = Worksheets("data").Range("Line" & CStr(I) & "SP").Value arrlngTotal(I) = Worksheets("data").Range("Line" & CStr(I) & "tot").Value Next I In the above example, the " Step 1" isn't necessary as the default is stepping one at a time going forward, but my general practice, I still like to keep things about as explicit as one can be given issues I ran into in the past with being implicit, not to mention changes that takes place from one version to the next such as from VB6 to VB.NET. Also, if your data type isn't long integer, then be sure to adjust your data type and variable name appropriately as needed. "Brad" wrote in message ... Hello, I need assistance with creating code for a loop (which i'm not good at at all). I have this code below that I'm only showing a portion of. I want to turn this into a For Loop code because I have to do the below code from "SP1", "Total1" through "SP30" and "Total30" so I'm looking at over 60 lines of code. How can I trun this into a For i = 1 to 30 type code? Any help is appreciated. Code: SP1.Value = Sheets("data").Range("Line1SP").Value Total1.Value = Sheets("data").Range("Line1tot").Value SP2.Value = Sheets("data").Range("Line2SP").Value Total2.Value = Sheets("data").Range("Line2tot").Value SP3.Value = Sheets("data").Range("Line3SP").Value Total3.Value = Sheets("data").Range("Line3tot").Value SP4.Value = Sheets("data").Range("Line4SP").Value Total4.Value = Sheets("data").Range("Line4tot").Value |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| statistics - guidance | Excel Worksheet Functions | |||
| Need some guidance | Excel Worksheet Functions | |||
| looking for logic guidance | Excel Programming | |||
| After much help from this forum, I still some guidance... | Excel Discussion (Misc queries) | |||
| Architectural guidance, please | Excel Programming | |||
Hybrid Mode
