Sub CalculateStatement()
rowend = Sheets("Statement").Cells(Rows.Count, 2).End(xlUp).Row
rowendlnsum = Sheets("Loan Summary").Cells(Rows.Count, 1).End(xlUp).Row
'sorts all data according to date from earliest to latest
ActiveWorkbook.Worksheets("Loan Summary").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Loan Summary").Sort.SortFields.Add Key:=Range( _
"C:C"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:= _
xlSortNormal
With ActiveWorkbook.Worksheets("Loan Summary").Sort
.SetRange Range(Cells(2, 1), Cells(rowendlnsum, 12))
.Header = xlNo
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Sheets("Statement").Activate
Range(Sheets("Statement").Cells(2, 4), Sheets("Statement").Cells(rowend, 36)).NumberFormat = "_($* #,##0.00_);_($* (#,##0.00);_($* ""-""??_);_(@_)"
For i = 2 To rowend
year_check = Sheets("Statement").Cells(i, 2)
ch = 0
For zl = 2 To rowendlnsum + 1
year_zl = Sheets("Loan Summary").Cells(zl, 3)
year_value = Year(year_zl)
If year_value = year_check Then
If ch = 0 Then
zl_st = zl
End If
ch = 1
Else
If ch = 1 Then
zl_end = zl - 1
Exit For
End If
End If
Next zl
If zl_st = 0 Or zl_end = 0 Then
Sheets("Statement").Cells(i, 6) = 0
Else
'sum of total one time fees
Sheets("Statement").Cells(i, 4) = Application.WorksheetFunction.Sum(Range(Sheets("Loan Summary").Cells(zl_st, 7), (Sheets("Loan Summary").Cells(zl_end, 7))))
'sum of one time fees UK
Sheets("Statement").Cells(i, 5) = Application.WorksheetFunction.Sum(Range(Sheets("Loan Summary").Cells(zl_st, 8), (Sheets("Loan Summary").Cells(zl_end, 8))))
'sum of one time fees HO
Sheets("Statement").Cells(i, 6) = Application.WorksheetFunction.Sum(Range(Sheets("Loan Summary").Cells(zl_st, 9), (Sheets("Loan Summary").Cells(zl_end, 9))))
Dim book_uk, book_ho As Variant
book_uk = Application.WorksheetFunction.SumIf(Range(Sheets("Loan Summary").Cells(zl_st, 19), Sheets("Loan Summary").Cells(zl_end, 19)), "UK Branch", [range(sheets("Loan Summary").cells(zl_st,16),sheets("Loan Summary").cells (zl_end,16))])
Sheets("Statement").Cells(i, 17) = book_uk
book_ho = Application.WorksheetFunction.SumIf(Range(Sheets("Loan Summary").Cells(zl_st, 19), Sheets("Loan Summary").Cells(zl_end, 19)), "Head Office", [range(sheets("Loan Summary").cells(zl_st,16),sheets("Loan Summary").cells (zl_end,16))])
Sheets("Statement").Cells(i, 18) = book_ho
End If
zl_st = 0
zl_end = 0
Next i
once i run the macro, i get an error saying: Unable to get the sumif property of the worksheet function class and i have no idea why or how to get around it. please help!!
Bookmarks