Sumif within VBA

Lee-Ann · #1 09-25-2017, 08:21 PM

Hello All,
I don't have any knowledge about VBA, so if someone can help I'll be thankful.
I have a single sheet of workbook for our daily sales data entry with 6 active columns (A-F). I group our sales per Invoice number. At the end of each invoice I have to add 3 rows of countif (in column E) and 3 rows of sumif (in column F) manually. I have tried to record a macro with countif and sumif formulas but as you can see no luck. It's messing up the sheet, specially because my data entry rows per each invoice are dynamic.
I would like to make things automatically, because our invoices can reach over 250 every day.
As you can see in my workbook (attached with VBA), I would like to have my calculations 1 row below after each invoice group.

The VBA that I have recorded is the following:

Code:

Sub sumif()
'
' Macro2 Macro
'

'
    Selection.End(xlDown).Select
    ActiveCell.Offset(2, 3).Range("A1").Select
    ActiveCell.FormulaR1C1 = "CATEGORY (A110)"
    ActiveCell.Offset(1, 0).Range("A1").Select
    ActiveCell.FormulaR1C1 = "CATEGORY (B220,C330)"
    ActiveCell.Offset(1, 0).Range("A1").Select
    ActiveCell.FormulaR1C1 = "CATEGORY (D440,E550)"
    ActiveCell.Offset(-2, 1).Range("A1").Select
    ActiveCell.FormulaR1C1 = "=COUNTIF(R[-7]C[-2]:R[-2]C[-2],""110"")& "" ITEMS"""
    ActiveCell.Offset(1, 0).Range("A1").Select
    ActiveCell.FormulaR1C1 = _
        "=COUNTIF(R[-8]C[-2]:R[-3]C[-2],""A220"")+COUNTIF(R[-8]C[-2]:R[-3]C[-2],""C330"")&""   ITEMS"""
    ActiveCell.Offset(1, 0).Range("A1").Select
    Selection.FormulaR1C1 = _
        "=COUNTIF(R[-9]C[-2]:R[-4]C[-2],""D440"")+COUNTIF(R[-9]C[-2]:R[-4]C[-2],""E550"")&""   ITEMS"""
    ActiveCell.Offset(-2, 1).Range("A1").Select
    ActiveCell.FormulaR1C1 = "=SUMIF(R[-7]C[-3]:R[-2]C[-3],""A110"",R[-7]C:R[-2]C)"
    ActiveCell.Offset(1, 0).Range("A1").Select
    Selection.FormulaR1C1 = _
        "=SUMIF(R[-8]C[-3]:R[-3]C[-3],""B220"",R[-8]C:R[-3]C)+SUMIF(R[-8]C[-3]:R[-3]C[-3],""C330"",R[-8]C:R[-3]C)"
    ActiveCell.Offset(1, 0).Range("A1").Select
    Selection.FormulaR1C1 = _
        "=SUMIF(R[-9]C[-3]:R[-4]C[-3],""D440"",R[-9]C:R[-4]C)+SUMIF(R[-9]C[-3]:R[-4]C[-3],""E550"",R[-9]C:R[-4]C)"
    ActiveCell.Offset(1, 0).Range("A1").Select
    Selection.End(xlToLeft).Select
End Sub

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
SUMIF with Or	Lluewhyn	Excel	6	02-17-2017 10:48 AM
Another SUMIF	OTPM	Excel	6	06-06-2016 03:00 AM
VBA sumif	Nisio07	Excel Programming	0	02-26-2016 07:53 AM
Need help sumif with variable for VBA	jingo	Excel Programming	4	01-23-2014 11:02 AM
Average if and sumif	jennamae	Excel	4	01-17-2014 05:10 AM