Insert formulas & copy them to variable size rows and columns

[DPP]

I have being struggling with this over 4 hrs. Could you please help me?

In "D3" I would need:
1) Sum the values of the range "E3" to the last column to the right. The number of accounts changes every month.
2) Copy the formula down to the last row (the last row changes every month)

In “E3” I would need:
1) Type in the formula that is in “E3”
2) Copy that formula to the last row and last column (the last row and column varies month over month)

Thank you in advance for your assistance,
DPP

[mehmetcik]

Because the number of columns and rows changes, you will need to use a macro to enter the formulae.

try this code:

Sub Macro1()

    Sheets("Sheet1").Select
    
    'This selects the last used cell on the spreadsheet,
    'I will modify it to get the last row in sheet 2 on the next line.
    Selection.SpecialCells(xlCellTypeLastCell).Select
    
    Rows("3:" & Application.Max(Selection.SpecialCells(xlCellTypeLastCell).Row, 3)).Delete Shift:=xlUp
    
    Sheets("Sheet2").Select
    records = Range("A65536").End(xlUp).Row - 2
    
    ActiveWorkbook.Worksheets("Sheet2").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Sheet2").Sort.SortFields.Add Key:=Range("A2:A1" & records + 2) _
        , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    ActiveWorkbook.Worksheets("Sheet2").Sort.SortFields.Add Key:=Range("C2:C1" & records + 2) _
        , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
        
    With ActiveWorkbook.Worksheets("Sheet2").Sort
        .SetRange Range("A1:D" & records + 2)
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With

    Range("E2:E" & records + 2).Select
    Selection.FormulaR1C1 = "=IF(RC[-4]=R[-1]C[-4],R[-1]C & "", "" &RC[-2],RC[-2])"
    Range("F2:F" & records + 2).FormulaR1C1 = "=IF(R[1]C[-5]=RC[-5],0,1)"
    
    Range("A2:F" & records + 2).Value = Range("A2:F" & records + 2).Value

    ActiveWorkbook.Worksheets("Sheet2").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Sheet2").Sort.SortFields.Add Key:=Range("F2:F" & records + 2) _
        , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("Sheet2").Sort
        .SetRange Range("A1:F" & records + 2)
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    
    Columns("F:F").Select
    Selection.Find(What:="1", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
        :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
        False, SearchFormat:=False).Activate

    Range("A" & ActiveCell.Row & ":B" & records + 2).Copy Destination:=Sheets("Sheet1").Range("A3")
    Range("E" & ActiveCell.Row & ":E" & records + 2).Copy Destination:=Sheets("Sheet1").Range("C3")

People = records - ActiveCell.Row + 3

    Range("F1:F" & records + 1).Value = Range("C2:C" & records + 2).Value

ActiveSheet.Range("$F$1:$F$" & records + 1).RemoveDuplicates Columns:=1, Header:=xlNo

    Accounts = Range("F1:F" & records).SpecialCells(xlCellTypeBlanks).Row - 1
    Range("F1:F" & Accounts).Copy
    Worksheets("Sheet1").Range("E2").PasteSpecial Transpose:=True
    
    Sheets("Sheet1").Select
    Range("D3:D" & 2 + People).FormulaR1C1 = "=SUM(RC[1]:RC[3])"
    Range("E3", Cells(2 + People, 4 + Accounts)).FormulaR1C1 = "=SUMIFS(Sheet2!C4,Sheet2!C3,Sheet1!R2C,Sheet2!C1,Sheet1!RC1)"
Range("A1").Select
End Sub

[jolivanes]

Which version of Excel do you have?
You attached an .xls file which is 2003 or before.
SUMIFS is not available in 2003. You can use SUMPRODUCT but that does not accept whole columns (like "A:A")
If you have 2007 or later, you can try this.

Sub Update()
    Dim lc As Long, lr As Long
    lc = Cells(2, Columns.Count).End(xlToLeft).Column
    lr = Cells(Rows.Count, 1).End(xlUp).Row
    Sheets("Sheet1").Range("D3:D" & lr).Formula = "=SUM(RC[1]:RC[" & lc & "])"
    Sheets("Sheet1").Range(Cells(3, 5), Cells(lr, lc)).Formula = "=SUMIFS(Sheet2!C4,Sheet2!C3,Sheet1!R2C,Sheet2!C1,Sheet1!RC1)"
End Sub

[DPP]

Hi Jolivanes,

I have Excel 2010. I would really aprreciate your help :O)

I am new to macros and I am still struggling with this workbook.

Thank you,
DPP

[jolivanes]

Click on the Button that is in cell A1.
The code is in Module 1. (Alt + F11 will show you the code in Module 1)