grantwilliams
Programmer
Hi,
Does anyone know (programmatically or otherwise) how to use a lookup to determine which cells should sum?
The circumstances are:
These are financial reports where the user enters which financial period the reports are being run for.
The forcasting for the remainder of the year needs to look up which cells correspond to the remaining periods of the financial year and sum these cells.
I've used something very similar previously to autofill the next month. Here is the code I used.
Cells D23 and E23 contained lookup formulas which determine which columns needed to be autofilled based on the financial period entered by the user.
TIA,
Grant
Does anyone know (programmatically or otherwise) how to use a lookup to determine which cells should sum?
The circumstances are:
These are financial reports where the user enters which financial period the reports are being run for.
The forcasting for the remainder of the year needs to look up which cells correspond to the remaining periods of the financial year and sum these cells.
I've used something very similar previously to autofill the next month. Here is the code I used.
Code:
Sub Report_Setup()
'
' Report_Setup Macro
'
Dim SelectCells, FillCells
Sheets("Data").Select
SelectCells = Range("D23")
FillCells = Range("D23") & ":" & Range("E23")
Sheets(Array("Sheet1", "Sheet2", "Sheet3", "Sheet4", "Sheet5", "Sheet6", _
"Sheet7", "Sheet8")).Select
ActiveWindow.SmallScroll Down:=-33
Columns(SelectCells).Select
Selection.AutoFill Destination:=Columns(FillCells), Type:=xlFillDefault
Columns(FillCells).Select
Columns(SelectCells).Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("Data").Activate
End Sub
Cells D23 and E23 contained lookup formulas which determine which columns needed to be autofilled based on the financial period entered by the user.
TIA,
Grant