Some more help with code...Averaging a range

[stephentebaldi]

So this is my code:

Worksheets("Sheet1").Activate
Dim rTmpRange As Range
Dim sRefAnt() As String
Dim numGPCR As Long
Dim ref As Long
numGPCR = InputBox("Enter the Number of GPCRs Tested")
ReDim sRefAnt(1 To numGPCR)
For ref = 1 To numGPCR
Set rTmpRange = Application.InputBox("Please Select Data for
Reference Antagonist " & ref, Type:=8)
sRefAnt(ref) = rTmpRange.Address
Next

Dim RefAnt As Range
RefAnt() = WorksheetFunction.Average(sRefAnt) 'This averages the
Reference antagonist values for calculation
Worksheets("Sheet2").Activate
Range("F" & ref) = RefAnt()

I am trying to be able to put in 2+ ranges in line 6 and then average
those ranges individually later on (in RefAnt) is this possible to do?
and if so is it just the way I am defining my RefAnt variable? Do I
need to let excel know that I want it to go from 1 to numGPCR?

 

[Dick Kusleika]

Stephen:

It seems like your averaging should be inside the loop, not outside. Try
this

Sub AvgRanges()

Dim aRanges() As Range
Dim lGpcr As Long
Dim i As Long

lGpcr = InputBox("Enter # of GPCRs tested.")
ReDim aRanges(1 To lGpcr)

For i = 1 To lGpcr
Set aRanges(i) = _
Application.InputBox("Select Range for " & i, , , , , , , 8)
Range("F" & i).Value = _
Application.WorksheetFunction.Average(aRanges(i))
Next i

End Sub

Add some error checking if the users clicks cancel on any of the input
boxes.