maintaining a variable's value outside of a for loop...

R

R Tanner

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
 
S

smartin

R said:
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

R Tanner

Try:

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

R Tanner said:
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

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

R Tanner

CURRPERCENTAGE.Offset(1, 0) = ActiveCell.Value
R Tanner said:
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

R Tanner

On Aug 29, 11:54 am, JLGWhiz <[email protected]>
wrote:
Try:
CURRPERCENTAGE.Offset(1, 0) = ActiveCell.Value
:
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...
 
J

JLGWhiz

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 said:
On Aug 29, 11:54 am, JLGWhiz <[email protected]>
wrote:

CURRPERCENTAGE.Offset(1, 0) = ActiveCell.Value
:
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

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...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top