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