So my question is how to store a range of averages. It seems as though
it will only calculate the averages and then put them into cells. My
code below is what I am trying to do

Worksheets("Sheet1").Activate
Dim TempGPCR1 As Range
Dim GPCR1data As Range
Dim i As Long
For i = 1 To numCpd1
Worksheets("Sheet1").Activate
Set TempGPCR1 = Application.InputBox("Please Select Data
for Compound " & i, Type:=8)
Worksheets("Sheet3").Activate
Range("A" & i + 1) =
Application.WorksheetFunction.Average(TempGPCR1(i) )
Next i

'This code collects data for the ligand at Emax
Worksheets("Sheet1").Activate
Dim Emax1 As Range
Set Emax1 = Application.InputBox("Please Select Data for
Emax", Type:=8)

AveEmax1 = WorksheetFunction.Average(RefAnt1)

'This code calculates the % Activation of the individual
compounds
Worksheets("Sheet2").Activate
Range("B" & i + 2).Value = Worksheets("Sheet3").Cell("A" & i +
1) / AveEmax1

Basically I would like to store the averages of TempGPCR1() as a range
and then at the bottom divide that range by the AveEmax1 which will
then be put into a range in sheet 2 as seen.

If it can't be stored as a range of averages, and has to be put into
cells for saving, how would I go about operating on multiple cells when
they are not in the sheet that I want them to end up in after they have
undergone the operation?

Sorry it's so long winded, but I appreciate any help you all can offer.

Stephen