Function or Macro for Variable Formula?

[Matt]

I have two documents, once which recaps the other. On the recap
document, depending on certain critera (in this case a store number
and the week it was visited) I need it to find data for the previous
13 weeks and average that data.

Each row is a different store, and each column from F thru AH is a
different week. Using the MATCH function, I can find the particular
row that store is listed in, and with another MATCH function I find
the column of the week it was visited. For example, I could know that
a particular store was in row 20 of the document, and the week it was
visited was column 25 (which is column Y). I would need to write a
fomula to get the average of the 13 cells before that in the
particular row (L20:X20).

It would look like: =AVERAGE('[DataWkbk.xls]DataSheet'!$L$20:$X$20)

but I need the formula to be used for every line with different values
for the row and column referenced.

Should I switch my format to the R1C1 format and then write it to look
like
=AVERAGE('[DataWkbk.xls]DataSheet'!R20C12:R20C24)

But in order to use that for every different store, it would need to
look like
=AVERAGE('[DataWkbk.xls]DataSheet'!
R(storerow)C(weekcolumn-13):R(storerow)C(weekcolumn-1))

Should I write a Function that creates that formula for me for each
store, or do I make a Macro that iterates through each store and fills
in the data? I'm not sure how to get it to work.


This is an example of what I have tried with no result:

Function StoreAvg(row, column) As Double

Dim xx As Double 'startColumn
Dim yy As Double 'endColumn

xx = column - 13
yy = column - 1

StoreAvg = WorksheetFunction.Average('[Workbook.xls]Sheet1'!
RrowCxx:RrowCyy)"


End Function

 

[Matt]

Found the solution myself:

Sub BlitzBeforeData()

Dim foundRow As Double
Dim foundColumn As Double
Dim lastRow As Long

Dim Count As Integer

Application.ScreenUpdating = False

lastRow = Cells(65000, 2).End(xlUp).Offset(0, 0).row

For Count = 2 To lastRow
Cells(Count, 11).Select

foundRow = Cells(Count, 8).Value
foundColumn = Cells(Count, 9).Value

ActiveCell.FormulaR1C1 = "=AVERAGE('[Workbook.xls]Sheet1'!R" & _
foundRow & "C" & (foundColumn - 13) & ":R" & foundRow & "C" &
(foundColumn - 1) & ")"

Next Count

Application.ScreenUpdating = True

End Sub