My Blog

Inserting Formulas

In continuation from here

Now let’s say we want to find out the total expenses that we had from Jan to April. What If we could also find what was out average spend? In this section we will find the total expenses and display it in row 6 and the average expense in row 7. See this code. I am just pasting the code for the click event of Button1 as the other parts remains unchanged.

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
    '~~> Add a New Workbook
    xlWorkBook = xlApp.Workbooks.Add

    '~~> Display Excel
    xlApp.Visible = True

    '~~> Set the relebant sheet that we want to work with
    xlWorkSheet = xlWorkBook.Sheets("Sheet1")

    With xlWorkSheet
        '~~> Directly type the values that we want
        .Range("A1").Value = "Month"
        .Range("A2").Value = "January"
        .Range("A3").Value = "February"
        .Range("A4").Value = "March"
        .Range("A5").Value = "April"

        .Range("B1").Value = "Money Spent"
        .Range("B2").Value = "1000.00"
        .Range("B3").Value = "1500.00"
        .Range("B4").Value = "1200.00"
        .Range("B5").Value = "1100.00"

        '~~> Set title for Total and Average Expenses
        .Range("A6").Value = "Total Expense"
        .Range("A7").Value = "Average Expense"

        '~~> Insert formulas
        .Range("B6").Formula = "=Sum(B2:B5)"
        .Range("B7").Formula = "=Average(B2:B5)"
    End With
End Sub

When you run the code, this is the output that you will get: