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