i think the wording of the last post was too confusing so
let me try again. i hope that i'm not reposting something
that's already been posted, so here it goes. i have 2
sheets: Sheet1 and Sheet2. on Sheet1 i select a group of
cells and save the addresses into a variable:

adrs = Range(Cells(firstcell), Cells(lastcell)).Address

now i want to switch to Sheet2 and use these cells. i want
to choose a cell on Sheet2 and set the value of this cell
equal to the average of the cells that i had selected on
Sheet1. my guess was to use

Cells(a cell).Value = "=AVERAGE(Sheet1! [adrs])"

or something of this sort, but i cant figure out the proper
commands. any ideas? thanks

Joel,

As you have a value for firstcell and lastcell ( I assume it of A1 form,
such a s C4, F2, etc), then you can plug that directly into the AVERAGE

Cells(a cell).Value WorksheetFunction.Average(Worksheets("Sheet1"). _
Range(firstcell & ":" & lastcell))

Not sure what a cell is though, Cells wants a row id and a column id.

--

HTH

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

"joel" wrote in message
...
i think the wording of the last post was too confusing so
let me try again. i hope that i'm not reposting something
that's already been posted, so here it goes. i have 2
sheets: Sheet1 and Sheet2. on Sheet1 i select a group of
cells and save the addresses into a variable:

adrs = Range(Cells(firstcell), Cells(lastcell)).Address

now i want to switch to Sheet2 and use these cells. i want
to choose a cell on Sheet2 and set the value of this cell
equal to the average of the cells that i had selected on
Sheet1. my guess was to use

Cells(a cell).Value = "=AVERAGE(Sheet1! [adrs])"

or something of this sort, but i cant figure out the proper
commands. any ideas? thanks

While I do have the values for the first and last cells, I
do not have them in the form of A1, C5, etc. The macro I'm
trying to create is for an arbitrary group of cells which
is why I store their addresses in variable form:

For col = 2 To totalcols Step 1
adrs = Sheets("sheet2").Range(Cells(2, col),
Cells(totalrows, col)).Address
sheets("sheet3").cells(2,col).value = "=AVERAGE(adrs)"
Next col

In this example, the variable "adrs" does not store/use the
Sheets("sheet2") part. In this way, when I try to use the
"adrs" in the second line
[ sheets("sheet3").cells(2,col).value = "=AVERAGE(adrs)" ],
it switches to sheets("sheet3") and takes the average of
the addresses on sheets("sheet3"), not from
sheets("sheet2") as I initially stated in the first line. I
hope I'm not makeing this too confusing. I tried what you
suggested, but i was getting errors with it. thanks for the
help!


-----Original Message-----
Joel,

As you have a value for firstcell and lastcell ( I assume
it of A1 form,
such a s C4, F2, etc), then you can plug that directly
into the AVERAGE

Cells(a cell).Value
WorksheetFunction.Average(Worksheets("Sheet1"). _
Range(firstcell & ":" & lastcell))

Not sure what a cell is though, Cells wants a row id and a
column id.

--

HTH

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

"joel" wrote in message
...
i think the wording of the last post was too confusing so
let me try again. i hope that i'm not reposting something
that's already been posted, so here it goes. i have 2
sheets: Sheet1 and Sheet2. on Sheet1 i select a group of
cells and save the addresses into a variable:

adrs = Range(Cells(firstcell), Cells(lastcell)).Address

now i want to switch to Sheet2 and use these cells. i want
to choose a cell on Sheet2 and set the value of this cell
equal to the average of the cells that i had selected on
Sheet1. my guess was to use

Cells(a cell).Value = "=AVERAGE(Sheet1! [adrs])"

or something of this sort, but i cant figure out the proper
commands. any ideas? thanks


.

Joel,

Regardless of the format, you don't need to get the address, and you can't
plug AVERAGE into VBA as I shjowed

For col = 2 To totalcols Step 1
sheets("sheet3").cells(2,col).value = "=WorksheetFunction. _
AVERAGE(Range(Cells(2, col),Cells(totalrows, col)))"
Next col


--

HTH

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

"joel" wrote in message
...
While I do have the values for the first and last cells, I
do not have them in the form of A1, C5, etc. The macro I'm
trying to create is for an arbitrary group of cells which
is why I store their addresses in variable form:

For col = 2 To totalcols Step 1
adrs = Sheets("sheet2").Range(Cells(2, col),
Cells(totalrows, col)).Address
sheets("sheet3").cells(2,col).value = "=AVERAGE(adrs)"
Next col

In this example, the variable "adrs" does not store/use the
Sheets("sheet2") part. In this way, when I try to use the
"adrs" in the second line
[ sheets("sheet3").cells(2,col).value = "=AVERAGE(adrs)" ],
it switches to sheets("sheet3") and takes the average of
the addresses on sheets("sheet3"), not from
sheets("sheet2") as I initially stated in the first line. I
hope I'm not makeing this too confusing. I tried what you
suggested, but i was getting errors with it. thanks for the
help!


-----Original Message-----
Joel,

As you have a value for firstcell and lastcell ( I assume
it of A1 form,
such a s C4, F2, etc), then you can plug that directly
into the AVERAGE

Cells(a cell).Value
WorksheetFunction.Average(Worksheets("Sheet1"). _
Range(firstcell & ":" & lastcell))

Not sure what a cell is though, Cells wants a row id and a
column id.

--

HTH

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

"joel" wrote in message
...
i think the wording of the last post was too confusing so
let me try again. i hope that i'm not reposting something
that's already been posted, so here it goes. i have 2
sheets: Sheet1 and Sheet2. on Sheet1 i select a group of
cells and save the addresses into a variable:

adrs = Range(Cells(firstcell), Cells(lastcell)).Address

now i want to switch to Sheet2 and use these cells. i want
to choose a cell on Sheet2 and set the value of this cell
equal to the average of the cells that i had selected on
Sheet1. my guess was to use

Cells(a cell).Value = "=AVERAGE(Sheet1! [adrs])"

or something of this sort, but i cant figure out the proper
commands. any ideas? thanks


.

Sorry to keep bugging you (i'm pretty new at excel). I
think that I understand what your're talking about, but I
dont think it's exactly what I'm looking for. I guess my
main problem is selecting the cells on Sheet2, then setting
the value of a cell on Sheet3 equal to the average of the
cells on Sheet2.
Here's a code example that i showed you before. It computes
the average of the cells (say B2:B6) that I selected on
Sheet2. The only problem is that instead of selecting
values of the cells with those addresses on Sheet2 (B2:B6
on Sheet2), it selects values of the cells with those
addresses on Sheet3 (B2:B6 on Sheet3).

For col = 2 To totalcols Step 1
adrs = Sheets("sheet2").Range(Cells(2, col),
Cells(totalrows, col)).Address
sheets("sheet3").cells(2,col).value = "=AVERAGE( " & adrs
& " )"
Next col

From what I understand, you say that

For col = 2 To totalcols Step 1
sheets("sheet3").cells(2,col).value = "=WorksheetFunction.
AVERAGE(Range(Cells(2, col),Cells(totalrows, col)))"
Next col

should work the same as the code above, and it does.
However, I still have the same problem of selecting the
proper cells from the proper sheet. I hope I haven't
overlooked something. Thanke for all the help so far.




-----Original Message-----
Joel,

Regardless of the format, you don't need to get the
address, and you can't
plug AVERAGE into VBA as I shjowed

For col = 2 To totalcols Step 1
sheets("sheet3").cells(2,col).value = "=WorksheetFunction. _
AVERAGE(Range(Cells(2, col),Cells(totalrows, col)))"
Next col


--

HTH

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

"joel" wrote in message
...
While I do have the values for the first and last cells, I
do not have them in the form of A1, C5, etc. The macro I'm
trying to create is for an arbitrary group of cells which
is why I store their addresses in variable form:

For col = 2 To totalcols Step 1
adrs = Sheets("sheet2").Range(Cells(2, col),
Cells(totalrows, col)).Address
sheets("sheet3").cells(2,col).value = "=AVERAGE(adrs)"
Next col

In this example, the variable "adrs" does not store/use the
Sheets("sheet2") part. In this way, when I try to use the
"adrs" in the second line
[ sheets("sheet3").cells(2,col).value = "=AVERAGE(adrs)" ],
it switches to sheets("sheet3") and takes the average of
the addresses on sheets("sheet3"), not from
sheets("sheet2") as I initially stated in the first line. I
hope I'm not makeing this too confusing. I tried what you
suggested, but i was getting errors with it. thanks for the
help!


-----Original Message-----
Joel,

As you have a value for firstcell and lastcell ( I assume
it of A1 form,
such a s C4, F2, etc), then you can plug that directly
into the AVERAGE

Cells(a cell).Value
WorksheetFunction.Average(Worksheets("Sheet1"). _
Range(firstcell & ":" & lastcell))

Not sure what a cell is though, Cells wants a row id and a
column id.

--

HTH

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

"joel" wrote in
message
...
i think the wording of the last post was too confusing so
let me try again. i hope that i'm not reposting something
that's already been posted, so here it goes. i have 2
sheets: Sheet1 and Sheet2. on Sheet1 i select a group of
cells and save the addresses into a variable:

adrs = Range(Cells(firstcell), Cells(lastcell)).Address

now i want to switch to Sheet2 and use these cells. i
want
to choose a cell on Sheet2 and set the value of this
cell
equal to the average of the cells that i had selected on
Sheet1. my guess was to use

Cells(a cell).Value = "=AVERAGE(Sheet1! [adrs])"

or something of this sort, but i cant figure out the
proper
commands. any ideas? thanks


.



.

Joel,

Is this what you want

For col = 2 To totalcols Step 1
sheets("sheet3").cells(2,col).value = "=WorksheetFunction. _
AVERAGE(Worksheets("Sheet2").Range(Cells(2, col),Cells(totalrows,
col)))"
Next col


--

HTH

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

"joel" wrote in message
...
Sorry to keep bugging you (i'm pretty new at excel). I
think that I understand what your're talking about, but I
dont think it's exactly what I'm looking for. I guess my
main problem is selecting the cells on Sheet2, then setting
the value of a cell on Sheet3 equal to the average of the
cells on Sheet2.
Here's a code example that i showed you before. It computes
the average of the cells (say B2:B6) that I selected on
Sheet2. The only problem is that instead of selecting
values of the cells with those addresses on Sheet2 (B2:B6
on Sheet2), it selects values of the cells with those
addresses on Sheet3 (B2:B6 on Sheet3).

For col = 2 To totalcols Step 1
adrs = Sheets("sheet2").Range(Cells(2, col),
Cells(totalrows, col)).Address
sheets("sheet3").cells(2,col).value = "=AVERAGE( " & adrs
& " )"
Next col

From what I understand, you say that

For col = 2 To totalcols Step 1
sheets("sheet3").cells(2,col).value = "=WorksheetFunction.
AVERAGE(Range(Cells(2, col),Cells(totalrows, col)))"
Next col

should work the same as the code above, and it does.
However, I still have the same problem of selecting the
proper cells from the proper sheet. I hope I haven't
overlooked something. Thanke for all the help so far.




-----Original Message-----
Joel,

Regardless of the format, you don't need to get the
address, and you can't
plug AVERAGE into VBA as I shjowed

For col = 2 To totalcols Step 1
sheets("sheet3").cells(2,col).value = "=WorksheetFunction. _
AVERAGE(Range(Cells(2, col),Cells(totalrows, col)))"
Next col


--

HTH

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

"joel" wrote in message
...
While I do have the values for the first and last cells, I
do not have them in the form of A1, C5, etc. The macro I'm
trying to create is for an arbitrary group of cells which
is why I store their addresses in variable form:

For col = 2 To totalcols Step 1
adrs = Sheets("sheet2").Range(Cells(2, col),
Cells(totalrows, col)).Address
sheets("sheet3").cells(2,col).value = "=AVERAGE(adrs)"
Next col

In this example, the variable "adrs" does not store/use the
Sheets("sheet2") part. In this way, when I try to use the
"adrs" in the second line
[ sheets("sheet3").cells(2,col).value = "=AVERAGE(adrs)" ],
it switches to sheets("sheet3") and takes the average of
the addresses on sheets("sheet3"), not from
sheets("sheet2") as I initially stated in the first line. I
hope I'm not makeing this too confusing. I tried what you
suggested, but i was getting errors with it. thanks for the
help!


-----Original Message-----
Joel,

As you have a value for firstcell and lastcell ( I assume
it of A1 form,
such a s C4, F2, etc), then you can plug that directly
into the AVERAGE

Cells(a cell).Value
WorksheetFunction.Average(Worksheets("Sheet1"). _
Range(firstcell & ":" & lastcell))

Not sure what a cell is though, Cells wants a row id and a
column id.

--

HTH

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

"joel" wrote in
message
...
i think the wording of the last post was too confusing so
let me try again. i hope that i'm not reposting something
that's already been posted, so here it goes. i have 2
sheets: Sheet1 and Sheet2. on Sheet1 i select a group of
cells and save the addresses into a variable:

adrs = Range(Cells(firstcell), Cells(lastcell)).Address

now i want to switch to Sheet2 and use these cells. i
want
to choose a cell on Sheet2 and set the value of this
cell
equal to the average of the cells that i had selected on
Sheet1. my guess was to use

Cells(a cell).Value = "=AVERAGE(Sheet1! [adrs])"

or something of this sort, but i cant figure out the
proper
commands. any ideas? thanks


.



.