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
Click to expand...

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
T=1
Click to expand...

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

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

R Tanner

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

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

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
Click to expand...
Click to expand...
No I get the error message on the selection.find statement...It says
'Object variable or with block variable not set'...
Click to expand...

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
Click to expand...

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
Click to expand...
:
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
Click to expand...

For I = 1 To 12
range("SP[DATE]").Select
Click to expand...
Selection.Find(What:=CURRPERCENTAGE.Value, After:=ActiveCell,
LookIn:=xlValues, _
LookAt:=xlPart, SearchOrder:=xlByColumns,
SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Offset(0,
T).Activate
Click to expand...
CURRPERCENTAGE.Offset(1, 0) = ActiveCell
Set CURRPERCENTAGE = CURRPERCENTAGE.Offset(0, 1)
Next
Set CURRPERCENTAGE = CURRPERCENTAGE.Offset(1, -12)
X = X + 2
Next
Click to expand...
No I get the error message on the selection.find statement...It says
'Object variable or with block variable not set'...
Click to expand...

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
Click to expand...

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...
Click to expand...
 

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

Similar Threads

Working with a Loop 7
Working With A Loop 2
Need help with correct syntax please 2
If Cells.Find returns run-time error '91': Object variableor With block variable not set 1
Easy answers for anyone 1
Find Question 1
Placing the MsgBox "No ID found" statement in my code?? 12
Can't get the macro to continue through a range 2

Top