Create a VBA for SUMIF function using 2 workbooks in Excel

October 17, 2016 at 13:50:10
Specs: Windows 7
Hi All - I am trying to create a SUMIF VBA which leverages a dynamic, unique list of charge codes in column "H" of Workbook 1 (sheets will vary) as its RANGE, All of column A in workbook 2 on sheet 1 (renamed "individual") as the CRITERIA, and the SUM RANGE of column "I" from Workbook 1 (same sheet). List fro column H workbook 1 change monthly and is based on a name in column "g". Data has been subtotaled & sorted according to column A - group number , and column G - name. The amount of rows in all columns mentioned will change monthly so my thought for the VBA function would be some kind of step or loop function to go through each value in my unique list and match formulas with values in columns "a" and "g" of workbook 1. The files look like the following:

Workbook 1
Sheet

A.................................G.................H.................I
1 Groupno...................Name...........Code............Total
2 24..........................Smith..............4A................3
3 24..........................Smith..............4B................4
4 24..........................Smith..............4C...............-1
5.........................Smith Total...............................6
6 24.........................Adams..............4A................1
7 24.........................Adams..............4B................5
8 24.........................Adams..............4C................3
9 24.................Adams Total..................................9

Workbook 2 (Individual Sheet)
Sheet 1 (Renamed "Individual")
A...................C...................D................E
1 Code.............Value.............Total.........SumValue
2 4A.................1.00..............VBA..........=C*D
3 4B....................50..............VBA..........=C*D
4 4C....................40..............VBA..........=C*D


Codes in Column A of Workbook 2 will increase monthly (new codes added each month) based on data in Workbook 1 column "G" VBA formula will be place in individual sheet column "D" and and auto fill VBA to fill down the the last code in column A.

THANKS FOR THE HELP AND SUGGESTION! :-)

message edited by gracie1989


See More: Create a VBA for SUMIF function using 2 workbooks in Excel

Reply ↓  Report •


#1
October 31, 2016 at 11:09:10
This is what I have come up with however I am still unable to get the VBA to continue to the Loop function once I get to line 14 (If mdname =....). What am i missing?

Option Explicit
Sub CustomSumIf()
Dim lastGrpRow, grpNum As Integer
Dim sumGrp As Long
Dim firstAddress As String
Dim g As Range
Dim mdname As String
lastGrpRow = Workbooks("Chapman, J @ LFP Lexington1.xlsm").Worksheets("individual").Range("A" & Rows.Count).End(xlUp).Row
For grpNum = 3 To lastGrpRow
With Workbooks("Group ReportM.xlsm").Worksheets("LFP Lexington").Columns("H")
Set g = .Find(Workbooks("Chapman, J @ LFP Lexington1.xlsm").Worksheets("individual").Range("A" & grpNum), lookat:=xlWhole)
If Not g Is Nothing Then
firstAddress = g.Address
mdname = Workbooks("Group ReportM.xlsm").Worksheets("LFP Lexington").Range("G" & Rows.Count).End(xlUp).Row
If mdname = "CHAPMAN MD, JAMES M" Then

Do
sumGrp = sumGrp + Workbooks("Group ReportM.xlsm").Worksheets("LFP Lexington").Range("I" & g.Row)
Set g = .FindNext(g)
Loop While Not g Is Nothing And g.Address <> firstAddress
End If
Workbooks("Chapman, J @ LFP Lexington1.xlsm").Worksheets("individual").Range("AD" & grpNum) = sumGrp
sumGrp = 0
End If
End With
Next
End Sub


Reply ↓  Report •

#2
October 31, 2016 at 11:17:48
This VBA calculates all counts in column I on workbooks 2 for the codes found on sheet 1 of workbook 1 column A. I want to add in a criteria as stated above to specific names in column G of workbook 2, sheet name (LFP Lexington) only.
Option Explicit
Sub CustomSumIf()
Dim lastGrpRow, grpNum As Integer
Dim sumGrp As Long
Dim firstAddress As String
Dim g As Range
lastGrpRow = Workbooks("Chapman, J @ LFP Lexington1.xlsm").Worksheets("individual").Range("A" & Rows.Count).End(xlUp).Row
For grpNum = 3 To lastGrpRow
With Workbooks("Group ReportM.xlsm").Worksheets("LFP Lexington").Columns("H")
Set g = .Find(Workbooks("Chapman, J @ LFP Lexington1.xlsm").Worksheets("individual").Range("A" & grpNum), lookat:=xlWhole)
If Not g Is Nothing Then
firstAddress = g.Address
Do
sumGrp = sumGrp + Workbooks("Group ReportM.xlsm").Worksheets("LFP Lexington").Range("I" & g.Row)
Set g = .FindNext(g)
Loop While Not g Is Nothing And g.Address <> firstAddress
End If
Workbooks("Chapman, J @ LFP Lexington1.xlsm").Worksheets("individual").Range("AD" & grpNum) = sumGrp
sumGrp = 0
End With
Next
End Sub

message edited by gracie1989


Reply ↓  Report •

Related Solutions


Ask Question