Macro coding help needed
 

I am slowly learning to build macros, but I need a little help with a few
intermediate steps. Kindly tell me the code for the steps detailed below:

First, let's say that cells G10 and G11 of Sheet A are equations whose
values I will use in the macro (see below). How do I define those as values
within the macro?

I will then go to a cell B4 of a worksheet called BOPSummary and then I want
to move my cursor down in column B by the number of rows given by cell G10
above. What is the code for that?

Starting at that cell location, I want to highlight an area that is (296
minus G10) rows down and then, also five more columns to the right (that is,
to column G). How do I code that?

After doing some things that don't move the cursor, I then want to move one
cell up and block off that cell plus five more cells to the right (to column
G). How do I code that?

After doing some things, I next want to highlight an area that is the
equivalent of holding down the shift key and then hitting the end key and
then the up arrow. How do I code that?

I then want to use that range to do a "set print area" and then a page setup
and, for the number of pages, I want to use the value in that cell G11
above. How do I code that?

Thanks,
Grace

Macro coding help needed
 

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Grace" wrote in message
...
I am slowly learning to build macros, but I need a little help with a few
intermediate steps. Kindly tell me the code for the steps detailed below:

First, let's say that cells G10 and G11 of Sheet A are equations whose
values I will use in the macro (see below). How do I define those as
values
within the macro?


val1 = Range("G10").Value
val2 = Range("G11").Value

I will then go to a cell B4 of a worksheet called BOPSummary and then I
want
to move my cursor down in column B by the number of rows given by cell G10
above. What is the code for that?

Worksheets("BOPSummary").Activate
Range("B4").Offset(val1,0).Select

Starting at that cell location, I want to highlight an area that is (296
minus G10) rows down and then, also five more columns to the right (that
is,
to column G). How do I code that?

Worksheets("BOPSummary").Activate
Range("B4").Offset(val1,0).Resize(296-val1,5)

After doing some things that don't move the cursor, I then want to move
one
cell up and block off that cell plus five more cells to the right (to
column
G). How do I code that?

Activecell.Offset(-1,0).Resize(1,5).Select

After doing some things, I next want to highlight an area that is the
equivalent of holding down the shift key and then hitting the end key and
then the up arrow. How do I code that?

I then want to use that range to do a "set print area" and then a page
setup
and, for the number of pages, I want to use the value in that cell G11
above. How do I code that?

Thanks,
Grace

Macro coding help needed
 

Bob (or anyone),

Range("B4").Offset(val1,0).Resize(296-val1,5)

The command above is showing in red when I cut and pasted it in, as I did
with your val1 definition. Could there be some syntax wrong? Also, you
forgot to answer the last two queries in my e-mail.

Thanks so much for your fine help.
G
..
"Bob Phillips" wrote in message
...


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Grace" wrote in message
...
I am slowly learning to build macros, but I need a little help with a
few
intermediate steps. Kindly tell me the code for the steps detailed
below:

First, let's say that cells G10 and G11 of Sheet A are equations whose
values I will use in the macro (see below). How do I define those as
values
within the macro?


val1 = Range("G10").Value
val2 = Range("G11").Value

I will then go to a cell B4 of a worksheet called BOPSummary and then I
want
to move my cursor down in column B by the number of rows given by cell
G10
above. What is the code for that?

Worksheets("BOPSummary").Activate
Range("B4").Offset(val1,0).Select

Starting at that cell location, I want to highlight an area that is (296
minus G10) rows down and then, also five more columns to the right (that
is,
to column G). How do I code that?

Worksheets("BOPSummary").Activate
Range("B4").Offset(val1,0).Resize(296-val1,5)

After doing some things that don't move the cursor, I then want to move
one
cell up and block off that cell plus five more cells to the right (to
column
G). How do I code that?

Activecell.Offset(-1,0).Resize(1,5).Select

After doing some things, I next want to highlight an area that is the
equivalent of holding down the shift key and then hitting the end key
and
then the up arrow. How do I code that?

I then want to use that range to do a "set print area" and then a page
setup
and, for the number of pages, I want to use the value in that cell G11
above. How do I code that?

Thanks,
Grace

Macro coding help needed
 

With regard to my last post in this thread, I think I figured out the last
two queries. I still need to know what is wrong with the resize command.
That's it.

G

"Grace" wrote in message
...
Bob (or anyone),

Range("B4").Offset(val1,0).Resize(296-val1,5)

The command above is showing in red when I cut and pasted it in, as I did
with your val1 definition. Could there be some syntax wrong? Also, you
forgot to answer the last two queries in my e-mail.

Thanks so much for your fine help.
G
.
"Bob Phillips" wrote in message
...


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Grace" wrote in message
...
I am slowly learning to build macros, but I need a little help with a
few
intermediate steps. Kindly tell me the code for the steps detailed
below:

First, let's say that cells G10 and G11 of Sheet A are equations whose
values I will use in the macro (see below). How do I define those as
values
within the macro?


val1 = Range("G10").Value
val2 = Range("G11").Value

I will then go to a cell B4 of a worksheet called BOPSummary and then
I
want
to move my cursor down in column B by the number of rows given by cell
G10
above. What is the code for that?

Worksheets("BOPSummary").Activate
Range("B4").Offset(val1,0).Select

Starting at that cell location, I want to highlight an area that is
(296
minus G10) rows down and then, also five more columns to the right
(that
is,
to column G). How do I code that?

Worksheets("BOPSummary").Activate
Range("B4").Offset(val1,0).Resize(296-val1,5)

After doing some things that don't move the cursor, I then want to
move
one
cell up and block off that cell plus five more cells to the right (to
column
G). How do I code that?

Activecell.Offset(-1,0).Resize(1,5).Select

After doing some things, I next want to highlight an area that is the
equivalent of holding down the shift key and then hitting the end key
and
then the up arrow. How do I code that?

I then want to use that range to do a "set print area" and then a page
setup
and, for the number of pages, I want to use the value in that cell G11
above. How do I code that?

Thanks,
Grace

Macro coding help needed
 

as shown:

Range("B4").Offset(val1,0).Resize(296-val1,5)

this does nothing.

You would need

Range("B4").Offset(val1,0).Resize(296-val1,5).Select

or

Range("B4").Offset(val1,0).Resize(296-val1,5).Interior.ColorIndex = 3

as examples - what you want would depend on what you are trying to do.

also, if that isn't the problem, then the problem is probably the value of
Val1. If it creates an illegal reference, then you would get an error.

--
Regards,
Tom Ogilvy



"Grace" wrote in message
...
With regard to my last post in this thread, I think I figured out the last
two queries. I still need to know what is wrong with the resize command.
That's it.

G

"Grace" wrote in message
...
Bob (or anyone),

Range("B4").Offset(val1,0).Resize(296-val1,5)

The command above is showing in red when I cut and pasted it in, as I
did
with your val1 definition. Could there be some syntax wrong? Also, you
forgot to answer the last two queries in my e-mail.

Thanks so much for your fine help.
G
.
"Bob Phillips" wrote in message
...


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Grace" wrote in message
...
I am slowly learning to build macros, but I need a little help with
a
few
intermediate steps. Kindly tell me the code for the steps detailed
below:

First, let's say that cells G10 and G11 of Sheet A are equations
whose
values I will use in the macro (see below). How do I define those
as
values
within the macro?


val1 = Range("G10").Value
val2 = Range("G11").Value

I will then go to a cell B4 of a worksheet called BOPSummary and
then
I
want
to move my cursor down in column B by the number of rows given by
cell
G10
above. What is the code for that?

Worksheets("BOPSummary").Activate
Range("B4").Offset(val1,0).Select

Starting at that cell location, I want to highlight an area that is
(296
minus G10) rows down and then, also five more columns to the right
(that
is,
to column G). How do I code that?

Worksheets("BOPSummary").Activate
Range("B4").Offset(val1,0).Resize(296-val1,5)

After doing some things that don't move the cursor, I then want to
move
one
cell up and block off that cell plus five more cells to the right
(to
column
G). How do I code that?

Activecell.Offset(-1,0).Resize(1,5).Select

After doing some things, I next want to highlight an area that is
the
equivalent of holding down the shift key and then hitting the end
key
and
then the up arrow. How do I code that?

I then want to use that range to do a "set print area" and then a
page
setup
and, for the number of pages, I want to use the value in that cell
G11
above. How do I code that?

Thanks,
Grace

Macro coding help needed
 

Grace.

I was only showing you how to get that required range. Depending upon what
you mean by highlight, you have to act upon the range, select it or
whatever.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Grace" wrote in message
...
Bob (or anyone),

Range("B4").Offset(val1,0).Resize(296-val1,5)

The command above is showing in red when I cut and pasted it in, as I did
with your val1 definition. Could there be some syntax wrong? Also, you
forgot to answer the last two queries in my e-mail.

Thanks so much for your fine help.
G
.
"Bob Phillips" wrote in message
...


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Grace" wrote in message
...
I am slowly learning to build macros, but I need a little help with a
few
intermediate steps. Kindly tell me the code for the steps detailed
below:

First, let's say that cells G10 and G11 of Sheet A are equations whose
values I will use in the macro (see below). How do I define those as
values
within the macro?


val1 = Range("G10").Value
val2 = Range("G11").Value

I will then go to a cell B4 of a worksheet called BOPSummary and then
I
want
to move my cursor down in column B by the number of rows given by cell
G10
above. What is the code for that?

Worksheets("BOPSummary").Activate
Range("B4").Offset(val1,0).Select

Starting at that cell location, I want to highlight an area that is
(296
minus G10) rows down and then, also five more columns to the right
(that
is,
to column G). How do I code that?

Worksheets("BOPSummary").Activate
Range("B4").Offset(val1,0).Resize(296-val1,5)

After doing some things that don't move the cursor, I then want to
move
one
cell up and block off that cell plus five more cells to the right (to
column
G). How do I code that?

Activecell.Offset(-1,0).Resize(1,5).Select

After doing some things, I next want to highlight an area that is the
equivalent of holding down the shift key and then hitting the end key
and
then the up arrow. How do I code that?

I then want to use that range to do a "set print area" and then a page
setup
and, for the number of pages, I want to use the value in that cell G11
above. How do I code that?

Thanks,
Grace

Macro coding help needed
 

I guess, by highlight, I figured you know I meant select! Sorry.

Thanks
G

"Bob Phillips" wrote in message
...
Grace.

I was only showing you how to get that required range. Depending upon what
you mean by highlight, you have to act upon the range, select it or
whatever.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Grace" wrote in message
...
Bob (or anyone),

Range("B4").Offset(val1,0).Resize(296-val1,5)

The command above is showing in red when I cut and pasted it in, as I
did
with your val1 definition. Could there be some syntax wrong? Also, you
forgot to answer the last two queries in my e-mail.

Thanks so much for your fine help.
G
.
"Bob Phillips" wrote in message
...


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Grace" wrote in message
...
I am slowly learning to build macros, but I need a little help with
a
few
intermediate steps. Kindly tell me the code for the steps detailed
below:

First, let's say that cells G10 and G11 of Sheet A are equations
whose
values I will use in the macro (see below). How do I define those
as
values
within the macro?


val1 = Range("G10").Value
val2 = Range("G11").Value

I will then go to a cell B4 of a worksheet called BOPSummary and
then
I
want
to move my cursor down in column B by the number of rows given by
cell
G10
above. What is the code for that?

Worksheets("BOPSummary").Activate
Range("B4").Offset(val1,0).Select

Starting at that cell location, I want to highlight an area that is
(296
minus G10) rows down and then, also five more columns to the right
(that
is,
to column G). How do I code that?

Worksheets("BOPSummary").Activate
Range("B4").Offset(val1,0).Resize(296-val1,5)

After doing some things that don't move the cursor, I then want to
move
one
cell up and block off that cell plus five more cells to the right
(to
column
G). How do I code that?

Activecell.Offset(-1,0).Resize(1,5).Select

After doing some things, I next want to highlight an area that is
the
equivalent of holding down the shift key and then hitting the end
key
and
then the up arrow. How do I code that?

I then want to use that range to do a "set print area" and then a
page
setup
and, for the number of pages, I want to use the value in that cell
G11
above. How do I code that?

Thanks,
Grace

Macro coding help needed
 

Well I sort of did, but I tried to be flexible in my approach. That will
teach me<g

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Grace" wrote in message
...
I guess, by highlight, I figured you know I meant select! Sorry.

Thanks
G

"Bob Phillips" wrote in message
...
Grace.

I was only showing you how to get that required range. Depending upon
what
you mean by highlight, you have to act upon the range, select it or
whatever.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Grace" wrote in message
...
Bob (or anyone),

Range("B4").Offset(val1,0).Resize(296-val1,5)

The command above is showing in red when I cut and pasted it in, as I
did
with your val1 definition. Could there be some syntax wrong? Also,
you
forgot to answer the last two queries in my e-mail.

Thanks so much for your fine help.
G
.
"Bob Phillips" wrote in message
...


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Grace" wrote in message
...
I am slowly learning to build macros, but I need a little help
with
a
few
intermediate steps. Kindly tell me the code for the steps
detailed
below:

First, let's say that cells G10 and G11 of Sheet A are equations
whose
values I will use in the macro (see below). How do I define those
as
values
within the macro?


val1 = Range("G10").Value
val2 = Range("G11").Value

I will then go to a cell B4 of a worksheet called BOPSummary and
then
I
want
to move my cursor down in column B by the number of rows given by
cell
G10
above. What is the code for that?

Worksheets("BOPSummary").Activate
Range("B4").Offset(val1,0).Select

Starting at that cell location, I want to highlight an area that
is
(296
minus G10) rows down and then, also five more columns to the right
(that
is,
to column G). How do I code that?

Worksheets("BOPSummary").Activate
Range("B4").Offset(val1,0).Resize(296-val1,5)

After doing some things that don't move the cursor, I then want to
move
one
cell up and block off that cell plus five more cells to the right
(to
column
G). How do I code that?

Activecell.Offset(-1,0).Resize(1,5).Select

After doing some things, I next want to highlight an area that is
the
equivalent of holding down the shift key and then hitting the end
key
and
then the up arrow. How do I code that?

I then want to use that range to do a "set print area" and then a
page
setup
and, for the number of pages, I want to use the value in that cell
G11
above. How do I code that?

Thanks,
Grace

Macro coding help needed
 

Perhaps I'm wrong but most people asking for coding help cannot be assumed
to otherwise know what the h' they are doing!

Thanks again!
G

"Bob Phillips" wrote in message
...
Well I sort of did, but I tried to be flexible in my approach. That will
teach me<g

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Grace" wrote in message
...
I guess, by highlight, I figured you know I meant select! Sorry.

Thanks
G

"Bob Phillips" wrote in message
...
Grace.

I was only showing you how to get that required range. Depending upon
what
you mean by highlight, you have to act upon the range, select it or
whatever.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Grace" wrote in message
...
Bob (or anyone),

Range("B4").Offset(val1,0).Resize(296-val1,5)

The command above is showing in red when I cut and pasted it in, as
I
did
with your val1 definition. Could there be some syntax wrong? Also,
you
forgot to answer the last two queries in my e-mail.

Thanks so much for your fine help.
G
.
"Bob Phillips" wrote in message
...


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Grace" wrote in message
...
I am slowly learning to build macros, but I need a little help
with
a
few
intermediate steps. Kindly tell me the code for the steps
detailed
below:

First, let's say that cells G10 and G11 of Sheet A are equations
whose
values I will use in the macro (see below). How do I define
those
as
values
within the macro?


val1 = Range("G10").Value
val2 = Range("G11").Value

I will then go to a cell B4 of a worksheet called BOPSummary and
then
I
want
to move my cursor down in column B by the number of rows given
by
cell
G10
above. What is the code for that?

Worksheets("BOPSummary").Activate
Range("B4").Offset(val1,0).Select

Starting at that cell location, I want to highlight an area that
is
(296
minus G10) rows down and then, also five more columns to the
right
(that
is,
to column G). How do I code that?

Worksheets("BOPSummary").Activate
Range("B4").Offset(val1,0).Resize(296-val1,5)

After doing some things that don't move the cursor, I then want
to
move
one
cell up and block off that cell plus five more cells to the
right
(to
column
G). How do I code that?

Activecell.Offset(-1,0).Resize(1,5).Select

After doing some things, I next want to highlight an area that
is
the
equivalent of holding down the shift key and then hitting the
end
key
and
then the up arrow. How do I code that?

I then want to use that range to do a "set print area" and then
a
page
setup
and, for the number of pages, I want to use the value in that
cell
G11
above. How do I code that?

Thanks,
Grace