Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

sum range before lastrow contains word

0

Hi

I  have  two  lastrow CUSTOMER & CLIENT   .every  time  I  will  insert rows  before lastrow  contains CUSTOMER & CLIENT  . what  I  want  it  sum  the  whole  range  before  lastrow CUSTOMER & CLIENT   and  put  the  TOTAL  in lastrow CUSTOMER & CLIENT for  columns C,D,E   should  deal  with  new rows where  insert before  lastrow CUSTOMER & CLIENT.

this  is  what  I  try  it  but  doesn't work  so  far .

Sub InsTots()
    Dim totRow As Long
    totRow = Cells(Rows.Count, 1).End(xlUp).Row + 1
    Cells(totRow, 2).Formula = "CUSTOMER"
    Cells(totRow, 3).Formula = "=SUM(C2:C" & totRow - 1 & ")"
    Cells(totRow, 4).Formula = "=SUM(D2:D" & totRow - 1 & ")"
     Cells(totRow, 5).Formula = "=SUM(E2:E" & totRow - 1 & ")"
    Cells(totRow, 2).Formula = "CLIENT"
    Cells(totRow, 3).Formula = "=SUM(C2:C" & totRow - 1 & ")"
    Cells(totRow, 4).Formula = "=SUM(D2:D" & totRow - 1 & ")"
     Cells(totRow, 5).Formula = "=SUM(E2:E" & totRow - 1 & ")"
End Sub
  
excel
MS Office Version: 2016
posted by: Kalil (rep: 36) on Sat Sep 3, 2022 at 4:48 pm
Answer
Discuss

Discussion

Hi Kalif

Sorry but I don't understand your question or your existing code- if A7 is the last used cell, your code would write CUSTOMER and add SUM formulae to cells B8:E8 but then overwrite those sames cells via the lines starting:
Cells(totRow, 2).Formula = "CLIENT"

Also, are you adding a variable number of new lines each time?

As usual, it would help to see an Excel file- please edit your question and use the Add Files... button to upload a representative Excel file (without any personal data) to show your existing macro and data. You might add a sheet showiung the expected result too. Then we should be able to give specific help (though that might not be today for me).
John_Ru (rep: 6142) Sep 4, '22 at 6:38 am
are you adding a variable number of new lines each time?
yes 
the  file  was  xlsb  seem  website  doesn't  accept  it . I  thought  I  uploaded . you  can  see  the  file .
Kalil (rep: 36) Sep 4, '22 at 9:17 am
Thanks for the file, Kalil- it helped (I think- please see my Answer)
John_Ru (rep: 6142) Sep 4, '22 at 10:08 am
Add to Discussion

Answers

0
Selected Answer

Hi Kalil

In the  revised file attached, I've modified your code (see below) using a simple loop to search for the first blank cell in column A (captured as new variable CustRow) and add formulae to sum the CUSTOMER range (from row 2). It then adds the CLIENT line formulae.

It's renamed to UpdateTots and I've added (sample)  key changes in bold plus comments for your guidance:

Sub UpdateTots()

    Dim totRow As Long, n As Long, CustRow As Long

    ' get row after last in column A
    totRow = Cells(Rows.Count, 1).End(xlUp).Row + 1

    ' loop to last used row (only) in column A
    For n = 2 To totRow - 1
        ' check for empty cell in column
        If Cells(n, 1) = "" Then
            ' write sum formula for CUSTOMER
            Cells(n, 2).Value = "CUSTOMER"
            Cells(n, 3).Formula = "=SUM(C2:C" & n - 1 & ")"
            Cells(n, 4).Formula = "=SUM(D2:D" & n - 1 & ")"
            Cells(n, 5).Formula = "=SUM(E2:E" & n - 1 & ")"
            'record this row
            CustRow = n
        End If
    Next n

    ' write sum formula for CLIENT
    Cells(totRow, 2).Value = "CLIENT"
    Cells(totRow, 3).Formula = "=SUM(C" & CustRow + 1 & ":C" & totRow - 1 & ")"
    Cells(totRow, 4).Formula = "=SUM(D" & CustRow + 1 & ":D" & totRow - 1 & ")"
    Cells(totRow, 5).Formula = "=SUM(E" & CustRow + 1 & ":E" & totRow - 1 & ")"
End Sub

Hope this fixes things for you.

posted by: John_Ru (rep: 6142) on Sun Sep 4, 2022 at 9:51 am
Discuss

Discussion

Forgot to say that I chose the first blank cell in A deliberately (in case it's the first time the macro is run and CUSTOMER is not yet in column B- as per the file I attached).   If it is already there, you could change the IF test to read: 
' check for CUSTOMER in column B
        If Cells(n, 2) = "CUSTOMER" Then
            ' write sum formula for CUSTOMER 
            Cells(n, 3).Formula = "=SUM(C2:C" & n - 1 & ")"
John_Ru (rep: 6142) Sep 4, '22 at 10:16 am
excellent work !
many  thanks  for  help
Kalil (rep: 36) Sep 5, '22 at 4:01 am
Thanks for selecting my answer, Kalif
John_Ru (rep: 6142) Sep 5, '22 at 5:53 am
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login