2nd attempt at excel VB commands
 

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

2nd attempt at excel VB commands
 

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

2nd attempt at excel VB commands
 

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


.

2nd attempt at excel VB commands
 

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


.

2nd attempt at excel VB commands
 

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


.



.

2nd attempt at excel VB commands
 

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


.



.

2nd attempt at excel VB commands
 

I'm getting a
Compiler error:
Expected: end of statement
and it hilights Sheet2 in the line:

sheets("sheet3").cells(2,col).value =
"=WorksheetFunction.AVERAGE(Worksheets("Sheet2").R ange(Cells(2,
col),Cells(totalrows,col)))"

I noticed you used both sheets("sheet3") and
Worksheets("sheet2") . What is the difference between using
Sheets and Worksheets. thanks



-----Original Message-----
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


.



.



.

2nd attempt at excel VB commands
 

Joel,

Sorry, I didn't test it, just constructed it. This is tested

For col = 2 To totalcols Step 1
With Worksheets("Sheet2")
Set aveRng = .Range(.Cells(2, col), .Cells(totalrows, col))
End With
Worksheets("Sheet3").Cells(2, col).Value = _
Application.Average(aveRng)
Next col

Sheets refers to all sheets in a workbook (worksheets, charts, Excel Macro,
Dialog, etc.), whereas Worksheets is just that one type. I tend to use
Worksheets to be specific (you can't average a chart as there are no cells
to average across), I just missed the other one.


--

HTH

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

wrote in message
...
I'm getting a
Compiler error:
Expected: end of statement
and it hilights Sheet2 in the line:

sheets("sheet3").cells(2,col).value =
"=WorksheetFunction.AVERAGE(Worksheets("Sheet2").R ange(Cells(2,
col),Cells(totalrows,col)))"

I noticed you used both sheets("sheet3") and
Worksheets("sheet2") . What is the difference between using
Sheets and Worksheets. thanks



-----Original Message-----
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


.



.



.

2nd attempt at excel VB commands
 

that's perfect! thanks a lot for all the help!



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

Sorry, I didn't test it, just constructed it. This is tested

For col = 2 To totalcols Step 1
With Worksheets("Sheet2")
Set aveRng = .Range(.Cells(2, col),
..Cells(totalrows, col))
End With
Worksheets("Sheet3").Cells(2, col).Value = _
Application.Average(aveRng)
Next col

Sheets refers to all sheets in a workbook (worksheets,
charts, Excel Macro,
Dialog, etc.), whereas Worksheets is just that one type. I
tend to use
Worksheets to be specific (you can't average a chart as
there are no cells
to average across), I just missed the other one.


--

HTH

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

wrote in message
...
I'm getting a
Compiler error:
Expected: end of statement
and it hilights Sheet2 in the line:

sheets("sheet3").cells(2,col).value =

"=WorksheetFunction.AVERAGE(Worksheets("Sheet2").R ange(Cells(2,
col),Cells(totalrows,col)))"

I noticed you used both sheets("sheet3") and
Worksheets("sheet2") . What is the difference between using
Sheets and Worksheets. thanks



-----Original Message-----
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


.



.



.



.

2nd attempt at excel VB commands
 

Great, got there in the end.

--

HTH

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

wrote in message
...
that's perfect! thanks a lot for all the help!



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

Sorry, I didn't test it, just constructed it. This is tested

For col = 2 To totalcols Step 1
With Worksheets("Sheet2")
Set aveRng = .Range(.Cells(2, col),
.Cells(totalrows, col))
End With
Worksheets("Sheet3").Cells(2, col).Value = _
Application.Average(aveRng)
Next col

Sheets refers to all sheets in a workbook (worksheets,
charts, Excel Macro,
Dialog, etc.), whereas Worksheets is just that one type. I
tend to use
Worksheets to be specific (you can't average a chart as
there are no cells
to average across), I just missed the other one.


--

HTH

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

wrote in message
...
I'm getting a
Compiler error:
Expected: end of statement
and it hilights Sheet2 in the line:

sheets("sheet3").cells(2,col).value =

"=WorksheetFunction.AVERAGE(Worksheets("Sheet2").R ange(Cells(2,
col),Cells(totalrows,col)))"

I noticed you used both sheets("sheet3") and
Worksheets("sheet2") . What is the difference between using
Sheets and Worksheets. thanks



-----Original Message-----
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


.



.



.



.