Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Function or Macro for Variable Formula?
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Function or Macro for Variable Formula?
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro Sum Formula For a Variable Rang | Excel Programming | |||
Need Help: 'sheets' function with a variable in a formula | Excel Worksheet Functions | |||
Need Help: 'sheets' function with a variable in a formula | Excel Programming | |||
Help with macro formula and variable | Excel Worksheet Functions | |||
Variable sized average macro/function | Excel Programming |