Microsoft Office 2007 professional (aca...

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 $30I 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!!!

My example didn't post very clearly: Sheet1

L M

1 $10

2 $20

3 $30Sheet2

E I

1 $3

1 $7

2 $17

2 $1

2 $2

3 $30

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.

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!!!

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.

DerbyDad: Thank You!!! YOU ARE MY HERO!!!!!!!

Ask Your Question

Weekly Poll