Microsoft Office Forums

Go Back   Microsoft Office Forums > >

 
 
Thread Tools Display Modes
Prev Previous Post   Next Post Next
  #1  
Old 09-25-2017, 08:21 PM
Lee-Ann Lee-Ann is offline Sumif within VBA Windows 7 64bit Sumif within VBA Office 2010 64bit
Novice
Sumif within VBA
 
Join Date: Sep 2017
Posts: 2
Lee-Ann is on a distinguished road
Default Sumif within VBA

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
Attached Files
File Type: xlsm SalesBook.xlsm (17.8 KB, 13 views)
Reply With Quote
 

Tags
excel vba, sumif in vba



Similar Threads
Thread Thread Starter Forum Replies Last Post
Sumif within VBA 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
Sumif within VBA Average if and sumif jennamae Excel 4 01-17-2014 05:10 AM

Other Forums: Access Forums

All times are GMT -7. The time now is 08:28 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2025, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2025 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft