![]() |
#1
|
|||
|
|||
![]()
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 |
Tags |
excel vba, sumif in vba |
|
![]() |
||||
Thread | Thread Starter | Forum | Replies | Last Post |
![]() |
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 |
![]() |
jennamae | Excel | 4 | 01-17-2014 05:10 AM |