Working With A Loop
 

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

Working With A Loop
 

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" wrote in message
...
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

Working With A Loop
 

On Sep 2, 10:45 am, "Bernie Deitrick" <deitbe @ consumer dot org
wrote:
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" wrote in message

...

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

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