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.

[find last row](http://stackoverflow.com/questions/11169445/error-finding-last-used-cell-in-vba) — Siddharth Rout, Dec 07 '13 at 16:48

Siddharth Rout · Accepted Answer · 2013-12-07T17:09:57.813

0

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

edited Dec 07 '13 at 17:09

answered Dec 07 '13 at 17:02

Siddharth Rout

147,039
17
206
250

Hi Siddhart, thank you very much for your reply, but when I copy the code and place it in vba project, I get the following error message: Compile Error, Invalid Inside Procedure. I don't know who to attach a file to this website, otherwise I would have uploaded the error file. If you can let me know your email address in my inbox, I will email you the screen print of the error message. Do I need to copy the whole of the above code and place it underneath "Keyboard Shortcut: Ctrl+Shift+L? Or do I need to have the above code only without Sub Macro6() and other bit? – Malik Dec 07 '13 at 17:27
Delete your 4 lines inside the `Sub Macro6()-End Sub` and copy the code between `Sub Sample() - End Sub` from my code and paste it there. – Siddharth Rout Dec 07 '13 at 17:30
Your comments within the code '~~>, would that make any difference if I leave them there or would I need to remove them after pasting the code? – Malik Dec 07 '13 at 17:41
leave them as it is :) – Siddharth Rout Dec 07 '13 at 17:42
Wow brilliant, its working fantastically. BUT, I am working on excel 2010 and when I copy the code, it get written in my "PERSONAL" excel file instead of that being available to different worksheets, as every week I run new files. Could you please guide me how can I have this macro available for new files. I think it has something to do with "this worksheet". Your help is much appreciated. – Malik Dec 07 '13 at 17:58
Replace `ThisWorkbook.Sheets("Sheet1")` with `ActiveSheet` – Siddharth Rout Dec 07 '13 at 17:59
Thank you very much Siddhart, I will copy and paste the working code in the Answers. All credit goes to you. Kind regards – Malik Dec 07 '13 at 18:13

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

1 Answers1