Working With A Loop

R

R Tanner

I posted something about this last Friday, but I have rewritten it and
found out specifically where my problem is at. For some reason, I am
having trouble updating the offset function in my selection.find
method. It works just fine until I perform this action.

To give you a clearer picture of what this code is supposed to be
doing:

It is part of a much larger macro that is writing out a dashboard for
me from a series of tables I have in other spreadsheets. This code is
identifying specific values in my table(located in the _2008_Tickets
spreadsheet) and putting them into a table in a different spreadsheet,
according to a month(which the user enters at the beginning of the
macro). The months are listed across the top of my table(i.e. For M =
1 to 12) and the indicators are listed along the left hand side
vertically(i.e. Do Until T = 10).

This line: CURRPERCENTAGE.Offset(X, 0) = ActiveCell is how my code
moves from row to row and consequently moves through the table.

This line: Set CURRPERCENTAGE = CURRPERCENTAGE.Offset(0, 1) is how I
move from column to column through my table.

This line: selection.find().offset(0,DD).activate is incremented by 2
because I want to pull every other value in that row out of the
table. The in between values are placed in a separate table. To
clarify: my table is located in columns WZU:XAN. In WZU, my code is
finding the month, then offsetting by 1 column 0 rows, pulling out the
value, then finding the next month, offsetting by the same amount,
extracting the value, until it gets to 12. The next step is to do the
same thing again, except offset by 3 columns 0 rows. It should do
this 10 times.

The error I get is object or with block variable not set. I only get
the error when I put in the DD variable. The problem is this is very
crucial to my loop. I need this variable or else I will have the same
variable 10 times for each month.

Thanks for your help guys. Hopefully I can figure this one out in
short order. I needed to solve it last week..:(

Set CURRPERCENTAGE = range("D174")

Sheets("_2008_Tickets").Select


X = 1
T = 1
DD = 1

Do Until T = 10
For M = 1 To 12
range("SP[DATE]").Select
Selection.Find(What:=CURRPERCENTAGE, After:=ActiveCell,
LookIn:=xlValues, _
LookAt:=xlPart, SearchOrder:=xlByColumns,
SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Offset(0,
DD).Activate
CURRPERCENTAGE.Offset(X, 0) = ActiveCell
MsgBox CURRPERCENTAGE.Offset(X, 0).Address
Set CURRPERCENTAGE = CURRPERCENTAGE.Offset(0, 1)
Next
Set CURRPERCENTAGE = CURRPERCENTAGE.Offset(0, -11)
T = T + 1
X = X + 1
DD = DD + 2
Loop
 
B

Bernie Deitrick

R.,

CURRPERCENTAGE.Offset(X, 0) = ActiveCell

actually means

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

It doesn't change the range object to point to another cell, the way you may think it might.

This line:

Set CURRPERCENTAGE = CURRPERCENTAGE.Offset(0, 1)

changes the range object CURRPERCENTAGE to be the next cell the the right. It doesn't set the value
the way you may think it does.

HTH,
Bernie
MS Excel MVP


R Tanner said:
I posted something about this last Friday, but I have rewritten it and
found out specifically where my problem is at. For some reason, I am
having trouble updating the offset function in my selection.find
method. It works just fine until I perform this action.

To give you a clearer picture of what this code is supposed to be
doing:

It is part of a much larger macro that is writing out a dashboard for
me from a series of tables I have in other spreadsheets. This code is
identifying specific values in my table(located in the _2008_Tickets
spreadsheet) and putting them into a table in a different spreadsheet,
according to a month(which the user enters at the beginning of the
macro). The months are listed across the top of my table(i.e. For M =
1 to 12) and the indicators are listed along the left hand side
vertically(i.e. Do Until T = 10).

This line: CURRPERCENTAGE.Offset(X, 0) = ActiveCell is how my code
moves from row to row and consequently moves through the table.

This line: Set CURRPERCENTAGE = CURRPERCENTAGE.Offset(0, 1) is how I
move from column to column through my table.

This line: selection.find().offset(0,DD).activate is incremented by 2
because I want to pull every other value in that row out of the
table. The in between values are placed in a separate table. To
clarify: my table is located in columns WZU:XAN. In WZU, my code is
finding the month, then offsetting by 1 column 0 rows, pulling out the
value, then finding the next month, offsetting by the same amount,
extracting the value, until it gets to 12. The next step is to do the
same thing again, except offset by 3 columns 0 rows. It should do
this 10 times.

The error I get is object or with block variable not set. I only get
the error when I put in the DD variable. The problem is this is very
crucial to my loop. I need this variable or else I will have the same
variable 10 times for each month.

Thanks for your help guys. Hopefully I can figure this one out in
short order. I needed to solve it last week..:(

Set CURRPERCENTAGE = range("D174")

Sheets("_2008_Tickets").Select


X = 1
T = 1
DD = 1

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

R Tanner

R.,

CURRPERCENTAGE.Offset(X, 0) = ActiveCell

actually means

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

It doesn't change the range object to point to another cell, the way you may think it might.

This line:

Set CURRPERCENTAGE = CURRPERCENTAGE.Offset(0, 1)

changes the range object CURRPERCENTAGE to be the next cell the the right. It doesn't set the value
the way you may think it does.

HTH,
Bernie
MS Excel MVP


I posted something about this last Friday, but I have rewritten it and
found out specifically where my problem is at. For some reason, I am
having trouble updating the offset function in my selection.find
method. It works just fine until I perform this action.
Click to expand...
To give you a clearer picture of what this code is supposed to be
doing:
Click to expand...
It is part of a much larger macro that is writing out a dashboard for
me from a series of tables I have in other spreadsheets. This code is
identifying specific values in my table(located in the _2008_Tickets
spreadsheet) and putting them into a table in a different spreadsheet,
according to a month(which the user enters at the beginning of the
macro). The months are listed across the top of my table(i.e. For M =
1 to 12) and the indicators are listed along the left hand side
vertically(i.e. Do Until T = 10).
Click to expand...
This line: CURRPERCENTAGE.Offset(X, 0) = ActiveCell is how my code
moves from row to row and consequently moves through the table.
Click to expand...
This line: Set CURRPERCENTAGE = CURRPERCENTAGE.Offset(0, 1) is how I
move from column to column through my table.
Click to expand...
This line: selection.find().offset(0,DD).activate is incremented by 2
because I want to pull every other value in that row out of the
table. The in between values are placed in a separate table. To
clarify: my table is located in columns WZU:XAN. In WZU, my code is
finding the month, then offsetting by 1 column 0 rows, pulling out the
value, then finding the next month, offsetting by the same amount,
extracting the value, until it gets to 12. The next step is to do the
same thing again, except offset by 3 columns 0 rows. It should do
this 10 times.
Click to expand...
The error I get is object or with block variable not set. I only get
the error when I put in the DD variable. The problem is this is very
crucial to my loop. I need this variable or else I will have the same
variable 10 times for each month.
Click to expand...
Thanks for your help guys. Hopefully I can figure this one out in
short order. I needed to solve it last week..:(
Click to expand...
Set CURRPERCENTAGE = range("D174")

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

"This line:

Set CURRPERCENTAGE = CURRPERCENTAGE.Offset(0, 1)

changes the range object CURRPERCENTAGE to be the next cell the the
right. It doesn't set the value
the way you may think it does. "

I want it to change the range object of this variable to the next cell
to the right...

"It doesn't change the range object to point to another cell, the way
you may think it might. "

I'm not sure what you mean by point to another cell - I want my
currpercentage to equal the value of my activecell. The following
code does not accomplish this?

CURRPERCENTAGE.Offset(X, 0) = ActiveCell

Also, the error was highlighting my use of the selection.find method
WHEN I used the DD variable...
 

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

maintaining a variable's value outside of a for loop... 6
Working with a Loop 7
Macro Loop 0
Change a great number of words in word files, with a macro as short as possible. 5
Countdown macro - display settings 2
Working outlook via vba code with xlsx - error 0
Setting column width in a table with a macro is not working 0
Wysiwyg in Word 0

Top