ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Programming (https://www.excelbanter.com/excel-programming/)
-   -   maintaining a variable's value outside of a for loop... (https://www.excelbanter.com/excel-programming/416298-maintaining-variables-value-outside-loop.html)

R Tanner
maintaining a variable's value outside of a for loop...
 
Can anyone think of why this would not work? The 'T' variable in my
offset function of the find method is not holding it's value...What do
I need to do different

T=1

For I = 1 To 12
range("SP[DATE]").Select


Selection.Find(What:=CURRPERCENTAGE.Value, After:=ActiveCell,
LookIn:=xlValues, _
LookAt:=xlPart, SearchOrder:=xlByColumns,
SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Offset(0,
T).Activate

CURRPERCENTAGE.Offset(1, 0) = ActiveCell
Set CURRPERCENTAGE = CURRPERCENTAGE.Offset(0, 1)
Next
Set CURRPERCENTAGE = CURRPERCENTAGE.Offset(1, -12)
X = X + 2
Next

JLGWhiz
maintaining a variable's value outside of a for loop...
 
Try:

CURRPERCENTAGE.Offset(1, 0) = ActiveCell.Value


"R Tanner" wrote:

Can anyone think of why this would not work? The 'T' variable in my
offset function of the find method is not holding it's value...What do
I need to do different

T=1

For I = 1 To 12
range("SP[DATE]").Select


Selection.Find(What:=CURRPERCENTAGE.Value, After:=ActiveCell,
LookIn:=xlValues, _
LookAt:=xlPart, SearchOrder:=xlByColumns,
SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Offset(0,
T).Activate

CURRPERCENTAGE.Offset(1, 0) = ActiveCell
Set CURRPERCENTAGE = CURRPERCENTAGE.Offset(0, 1)
Next
Set CURRPERCENTAGE = CURRPERCENTAGE.Offset(1, -12)
X = X + 2
Next

smartin
maintaining a variable's value outside of a for loop...
 
R Tanner wrote:
Can anyone think of why this would not work? The 'T' variable in my
offset function of the find method is not holding it's value...What do
I need to do different

T=1

For I = 1 To 12
range("SP[DATE]").Select


Selection.Find(What:=CURRPERCENTAGE.Value, After:=ActiveCell,
LookIn:=xlValues, _
LookAt:=xlPart, SearchOrder:=xlByColumns,
SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Offset(0,
T).Activate

CURRPERCENTAGE.Offset(1, 0) = ActiveCell
Set CURRPERCENTAGE = CURRPERCENTAGE.Offset(0, 1)
Next
Set CURRPERCENTAGE = CURRPERCENTAGE.Offset(1, -12)
X = X + 2
Next



In this code, it looks like T will always =1. Is that not happening? You
could try setting a watch to break when T < 1.

R Tanner
maintaining a variable's value outside of a for loop...
 
On Aug 29, 11:54 am, JLGWhiz
wrote:
Try:

CURRPERCENTAGE.Offset(1, 0) = ActiveCell.Value

"R Tanner" wrote:
Can anyone think of why this would not work? The 'T' variable in my
offset function of the find method is not holding it's value...What do
I need to do different

T=1

For I = 1 To 12
range("SP[DATE]").Select

Selection.Find(What:=CURRPERCENTAGE.Value, After:=ActiveCell,
LookIn:=xlValues, _
LookAt:=xlPart, SearchOrder:=xlByColumns,
SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Offset(0,
T).Activate

CURRPERCENTAGE.Offset(1, 0) = ActiveCell
Set CURRPERCENTAGE = CURRPERCENTAGE.Offset(0, 1)
Next
Set CURRPERCENTAGE = CURRPERCENTAGE.Offset(1, -12)
X = X + 2
Next

No I get the error message on the selection.find statement...It says
'Object variable or with block variable not set'...

R Tanner
maintaining a variable's value outside of a for loop...
 
On Aug 29, 12:08 pm, R Tanner wrote:
On Aug 29, 11:54 am, JLGWhiz
wrote:



Try:

CURRPERCENTAGE.Offset(1, 0) = ActiveCell.Value

"R Tanner" wrote:
Can anyone think of why this would not work? The 'T' variable in my
offset function of the find method is not holding it's value...What do
I need to do different

T=1

For I = 1 To 12
range("SP[DATE]").Select

Selection.Find(What:=CURRPERCENTAGE.Value, After:=ActiveCell,
LookIn:=xlValues, _
LookAt:=xlPart, SearchOrder:=xlByColumns,
SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Offset(0,
T).Activate

CURRPERCENTAGE.Offset(1, 0) = ActiveCell
Set CURRPERCENTAGE = CURRPERCENTAGE.Offset(0, 1)
Next
Set CURRPERCENTAGE = CURRPERCENTAGE.Offset(1, -12)
X = X + 2
Next

No I get the error message on the selection.find statement...It says
'Object variable or with block variable not set'...

I'm sorry...This is what it should have been...each time through the
loop, the value of T is changed...then when this loop is finished, the
program enters another loop where the value of the variable T is reset
back to 1 again...
T=1

For D = 1 To 12
range("SP[DATE]").Select
Selection.Find(What:=CURRPERCENTAGE.Value, After:=ActiveCell,
LookIn:=xlValues, _
LookAt:=xlPart, SearchOrder:=xlByColumns,
SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Offset(0, 1).Activate
CURRPERCENTAGE.Offset(T, 0) = ActiveCell
Set CURRPERCENTAGE = CURRPERCENTAGE.Offset(0, 1)
T = T + 1
Next

R Tanner
maintaining a variable's value outside of a for loop...
 
On Aug 29, 12:19 pm, R Tanner wrote:
On Aug 29, 12:08 pm, R Tanner wrote:



On Aug 29, 11:54 am, JLGWhiz
wrote:

Try:

CURRPERCENTAGE.Offset(1, 0) = ActiveCell.Value

"R Tanner" wrote:
Can anyone think of why this would not work? The 'T' variable in my
offset function of the find method is not holding it's value...What do
I need to do different

T=1

For I = 1 To 12
range("SP[DATE]").Select

Selection.Find(What:=CURRPERCENTAGE.Value, After:=ActiveCell,
LookIn:=xlValues, _
LookAt:=xlPart, SearchOrder:=xlByColumns,
SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Offset(0,
T).Activate

CURRPERCENTAGE.Offset(1, 0) = ActiveCell
Set CURRPERCENTAGE = CURRPERCENTAGE.Offset(0, 1)
Next
Set CURRPERCENTAGE = CURRPERCENTAGE.Offset(1, -12)
X = X + 2
Next

No I get the error message on the selection.find statement...It says
'Object variable or with block variable not set'...

I'm sorry...This is what it should have been...each time through the
loop, the value of T is changed...then when this loop is finished, the
program enters another loop where the value of the variable T is reset
back to 1 again...
T=1

For D = 1 To 12
range("SP[DATE]").Select
Selection.Find(What:=CURRPERCENTAGE.Value, After:=ActiveCell,
LookIn:=xlValues, _
LookAt:=xlPart, SearchOrder:=xlByColumns,
SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Offset(0, 1).Activate
CURRPERCENTAGE.Offset(T, 0) = ActiveCell
Set CURRPERCENTAGE = CURRPERCENTAGE.Offset(0, 1)
T = T + 1
Next

I declared T as an integer....That IS correct right??? I am at a
loss...It seems like I have been having weird things happen with my
code lately like this...like the other day my code just quit working
in the middle of the sub...so weird...

JLGWhiz
maintaining a variable's value outside of a for loop...
 
In the context that you are using T, you do not need to declare it, unless
you have declared Option Explicit. But your code is confusing, You have the
statement:

For D = 1 To 12

But D does not appear in the code. If you meant to use T in that statement,
then you do not neet the T = T + 1 statement within the loop. The
For...Next statement will automatically step through and increment T. If the
For D statement is intentional then you need to change something in your to
reflect the D variable.

"R Tanner" wrote:

On Aug 29, 12:19 pm, R Tanner wrote:
On Aug 29, 12:08 pm, R Tanner wrote:



On Aug 29, 11:54 am, JLGWhiz
wrote:

Try:

CURRPERCENTAGE.Offset(1, 0) = ActiveCell.Value

"R Tanner" wrote:
Can anyone think of why this would not work? The 'T' variable in my
offset function of the find method is not holding it's value...What do
I need to do different

T=1

For I = 1 To 12
range("SP[DATE]").Select

Selection.Find(What:=CURRPERCENTAGE.Value, After:=ActiveCell,
LookIn:=xlValues, _
LookAt:=xlPart, SearchOrder:=xlByColumns,
SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Offset(0,
T).Activate

CURRPERCENTAGE.Offset(1, 0) = ActiveCell
Set CURRPERCENTAGE = CURRPERCENTAGE.Offset(0, 1)
Next
Set CURRPERCENTAGE = CURRPERCENTAGE.Offset(1, -12)
X = X + 2
Next

No I get the error message on the selection.find statement...It says
'Object variable or with block variable not set'...

I'm sorry...This is what it should have been...each time through the
loop, the value of T is changed...then when this loop is finished, the
program enters another loop where the value of the variable T is reset
back to 1 again...
T=1

For D = 1 To 12
range("SP[DATE]").Select
Selection.Find(What:=CURRPERCENTAGE.Value, After:=ActiveCell,
LookIn:=xlValues, _
LookAt:=xlPart, SearchOrder:=xlByColumns,
SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Offset(0, 1).Activate
CURRPERCENTAGE.Offset(T, 0) = ActiveCell
Set CURRPERCENTAGE = CURRPERCENTAGE.Offset(0, 1)
T = T + 1
Next

I declared T as an integer....That IS correct right??? I am at a
loss...It seems like I have been having weird things happen with my
code lately like this...like the other day my code just quit working
in the middle of the sub...so weird...


All times are GMT +1. The time now is 05:32 PM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com