Find a series, then average

[baer.steven]

I'm new to VB, so I apologize in advance for what may be a simple
problem:

I'm trying to search a worksheet, based on the intersection of column
and row headers, for a series of cells, then average the numbers in
those cells, and output that average to a different worksheet.

Any help would be much appreciated.

SB

 

[Tom Ogilvy]

assume Row 1 of the data sheet contains headers like (starting in B1)
Col1 Col2 Col3 . . .
Assume Column A has headers like

rw1
rw2
rw3
rw4

starting in A2

Sub GetAverage()
Dim rHdr(1 to 3, 1 to 2) as String
Dim res as Variant, res1 as Variant
Dim r(1 to 3) as double
rHdr(1,1) = "Col1": rHdr(1,2) = "Rw3"
rHdr(2,1) = "Col5": rHdr(2,2) = "Rw7"
rHdr(3,1) = "Col10": rHdr(2,3) = "Rw9"

for i = 1 to 3
With Worksheets("Data")
res = application.Match(rHdr(i,1),.Rows(1),0)
res1 = application.Match(rHdr(i,2),.Columns(1),0)
End With
set rng = Intersect(cells(1,res).EntireColumn,cells(res1,1).EntireRow)
r(i) = rng.Value
Next

worksheets("Output").Range("F9").Value = Application.Average(r)

end Sub