Find Last Empty Cell in a Column & then Sum all the Cells Above

Question

I am new to VB in Excel and need help.

I have a spread sheet of Nominal Journal. Column B consists of Nominal Codes. Column J consists of debits whereas, Column K consists of Credits.

Every week depending on the number of transactions, the length of rows occupied by data will be different i.e., in one week, we could have data from row # 01 to row # 100, in another week we could have data from row 01 to row 180 or row 85.

The first row is the header row followed by the data.

My Questions:

I want to find the last row in column J and K and move the cursor in the next empty cell. For example if the last row is row # 100, then I want the cursor to move into J101. I have achieved this.
I want to do a sum total of column J in cell no J101 of all those rows whose nominal code begins with "7".
Likewise, I want a sum total of column K in cell K101 of all those rows whose nominal code begins with "7".

I have written the following code but the problem is when I run the macro, the sumproduct function takes into account the cell in which the total is going to be placed, i.e., the sum range is from B2:B101 instead of B2:B100 and J2:J101 instead of J2:J100.

Following is the copy of the code:

Sub Macro6()
'
' Macro6 Macro
' Column Totals
'
' Keyboard Shortcut: Ctrl+Shift+L
'
    Range("J1").Select
    ActiveCell.End(xlDown).Offset(1, 0).Select
    ActiveCell.FormulaR1C1 = "=SUMPRODUCT((LEFT(R2C2:RC2,1)+0=7)*(R2C:RC))"
    Range("J143").Select
End Sub

Any Help will be much appreciated.

Siddharth Rout Siddharth Rout · Accepted Answer · 2013-12-07T17:02:49

Is this what you are trying? I have commented the code, so you shouldn't have problem understanding the code. But if you still do then post back.

Option Explicit

Sub Sample()
    Dim ws As Worksheet
    Dim lRow As Long, frmlaRow As Long

    '~~> Set this to the relevant worksheet
    Set ws = ThisWorkbook.Sheets("Sheet1")

    With ws
        '~~> Find the last row in Col J
        lRow = .Range("J" & .Rows.Count).End(xlUp).Row
        '~~> row where the formula will be inserted
        frmlaRow = lRow + 1

        .Range("J" & frmlaRow).Formula = "=SUMPRODUCT((LEFT($B$2:$B$" & lRow & _
                                     ",1)=7)*($J$2:$J$" & lRow & "))"

        '~~> Find the last row in Col K
        lRow = .Range("K" & .Rows.Count).End(xlUp).Row
        '~~> row where the formula will be inserted
        frmlaRow = lRow + 1

        .Range("K" & frmlaRow).Formula = "=SUMPRODUCT((LEFT($B$2:$B$" & lRow & _
                                     ",1)=7)*($K$2:$K$" & lRow & "))"
    End With
End Sub

Find Last Empty Cell in a Column & then Sum all the Cells Above

1 Answers