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

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

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

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

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

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