VBA SUMIF Criteria/Ranges in different sheets

Microsoft Office 2007 professional (aca...
January 25, 2011 at 19:41:08
Specs: Microsoft Office 2007
Hi All - I have tried and failed multiple times at writing a potential code and have looked extensively across the web for help on this question and have found no answer: I hope somebody will be able to help me!

I am trying to create a SUMIF VBA code which leverages a dynamic, unique list from column L in sheet1 as its criteria and columns E and I in sheet2 as its range and sum range. I want to have the answer for the SUMIF code populate in column M next to the unique list in sheet1.

The amount of rows in each of the aforementioned columns are subject to change daily, so I know I need to include some sort of step or loop function to go through each value in my unique list. The file would look like such:

Sheet1: Sheet 2:
L M E I
1 $10 1 $3
2 $20 1 $7
3 $30 2 $17
2 $1
2 $2
3 $30

I realize that this would be a very easy formula to write in excel and copy down for each unique value:

(Cell M3 =sumif(sheet2!$E$4:$E$65536,sheet1!L3,sheet2!$I$4:$I$65536)

Unfortunately, I am worried about the excel savvy of its potential end user - which makes writing a VBA macro a must.

Can anyone help me?? Please feel free to ask me any questions if I was not clear

Thanks,
- Frustrated!!!


See More: VBA SUMIF Criteria/Ranges in different sheets

Report •


#1
January 25, 2011 at 19:43:24
My example didn't post very clearly:

Sheet1
L M
1 $10
2 $20
3 $30

Sheet2
E I
1 $3
1 $7
2 $17
2 $1
2 $2
3 $30


Report •

#2
January 25, 2011 at 21:36:39
Please read the How To referenced in my signature for instructions on how to post data in this forum.

Please include Row numbers when you repost your data since by your formula it appears that at least one of the lists do not start in Row 1.

Since the VBA code will need to be specific to the ranges in question, we need to know the Row numbers, not just the columns letters.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#3
January 26, 2011 at 05:07:53
Apologies - I think this format will work out better:
Sheet1

         A  . . . . . . L         M
1  Summary
2                       Group   Price
3                         1        $10
4                         2        $20
5                         3        $30
6 to Infinity            etc.      etc.


Sheet2 

      A    ............. E  ..........  I
1  Location
2  Date 
3  ....................Group .........Price
4                       1               $7
5                       1               $3
6                       2               $17
7                       2               $2
8                       2               $1
9                       3               $30
10 to Inf.              #               $##

Again to note: the last row in each of my sheets is referenced to go until "Infinity" because the amount of groups making purchases each day is subject to change. Thank you!!!


Report •

Related Solutions

#4
January 26, 2011 at 07:20:04
Try this:

Option Explicit
Sub CustomSumIf()
Dim lastGrpRow, grpNum As Integer
Dim sumGrp As Long
Dim firstAddress As String
Dim g As Range
 lastGrpRow = Sheets(1).Range("L" & Rows.Count).End(xlUp).Row
   For grpNum = 3 To lastGrpRow
     With Sheets(2).Columns("E")
      Set g = .Find(Sheets(1).Range("L" & grpNum), lookat:=xlWhole)
        If Not g Is Nothing Then
          firstAddress = g.Address
            Do
              sumGrp = sumGrp + Sheets(2).Range("I" & g.Row)
              Set g = .FindNext(g)
            Loop While Not g Is Nothing And g.Address <> firstAddress
        End If
       Sheets(1).Range("M" & grpNum) = sumGrp
       sumGrp = 0
     End With
   Next
End Sub

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#5
January 26, 2011 at 07:53:50
DerbyDad: Thank You!!! YOU ARE MY HERO!!!!!!!

Report •

#6
January 26, 2011 at 08:05:03
Glad I could help.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •


Ask Question