Hello All,

First post on the boards, so please bear with me. I'm stuck with a section of code involving a sumif function. I'm a beginner VBA user but have an above average Excel 2007 & 2010 base. I have 3 named ranges in my spreadsheet, NDCRange, UniqueNDCs, and IASTQTY. NDCRange contains a long list of product codes, UniqueNDCs contains the unique product codes from that list, and IASTQTY contains a quantity of products corresponding to each row in the NDCRange. My sumif needs to take each cell in the UniqueNDCs range and calculate the total quantity. For example:

NDCRange IASTQTY UniqueNDCs

111111 5 111111

111111 5 111112

111113 10 111113

111112 5

111112 15

My result needs to be:

111111 10

111112 20

111113 10

So far I have been able to correctly get the first sumif calculated correctly, however, it gives the same result for all the cells in the UniqueNDCs range. My problem is figuring out how to get the sumif criteria to loop through all the cells in the UniqueNDCs range. I've tried modifying the criterian = range ("UniqueNDCs").value

Below is my code:

Sub IASTSums()

Dim IAST As Worksheet

Dim EXP As Worksheet

Dim NDCRange As Range

Dim UniqueNDCs As Range

Dim IASTQTY As Range

Dim IASTResult As Long

Dim Cell As Range

Dim Criterian

Criterian = Worksheets("IAST").Range("AH2").Value

IASTResult = WorksheetFunction.SumIf(Range("NDCRange"), Criterian, Range("IASTQTY"))

Worksheets("IAST").Select

For Each Cell In Range("UniqueNDCs")

ActiveCell.Value = IASTResult

ActiveCell.Offset(1, 0).Select

Next Cell

End Sub

Any help would be greatly appreciated! This is part of a larger macro and needs to be automated because the end user doesn't even know how to copy down the formula.

Thanks so much,

JD

First post on the boards, so please bear with me. I'm stuck with a section of code involving a sumif function. I'm a beginner VBA user but have an above average Excel 2007 & 2010 base. I have 3 named ranges in my spreadsheet, NDCRange, UniqueNDCs, and IASTQTY. NDCRange contains a long list of product codes, UniqueNDCs contains the unique product codes from that list, and IASTQTY contains a quantity of products corresponding to each row in the NDCRange. My sumif needs to take each cell in the UniqueNDCs range and calculate the total quantity. For example:

NDCRange IASTQTY UniqueNDCs

111111 5 111111

111111 5 111112

111113 10 111113

111112 5

111112 15

My result needs to be:

111111 10

111112 20

111113 10

So far I have been able to correctly get the first sumif calculated correctly, however, it gives the same result for all the cells in the UniqueNDCs range. My problem is figuring out how to get the sumif criteria to loop through all the cells in the UniqueNDCs range. I've tried modifying the criterian = range ("UniqueNDCs").value

Below is my code:

Sub IASTSums()

Dim IAST As Worksheet

Dim EXP As Worksheet

Dim NDCRange As Range

Dim UniqueNDCs As Range

Dim IASTQTY As Range

Dim IASTResult As Long

Dim Cell As Range

Dim Criterian

Criterian = Worksheets("IAST").Range("AH2").Value

IASTResult = WorksheetFunction.SumIf(Range("NDCRange"), Criterian, Range("IASTQTY"))

Worksheets("IAST").Select

For Each Cell In Range("UniqueNDCs")

ActiveCell.Value = IASTResult

ActiveCell.Offset(1, 0).Select

Next Cell

End Sub

Any help would be greatly appreciated! This is part of a larger macro and needs to be automated because the end user doesn't even know how to copy down the formula.

Thanks so much,

JD

It sounds like this isn't an option, but a couple of formulas:

=IF(OFFSET(UniqueNDCs,ROW(1:1)-1,0)>0,OFFSET(UniqueNDCs,ROW(1:1)-1,0),"")

and

=SUMIF(NDCRange,A2,IASTQTY)

Since that isn't an option for you, a bit of VBA. Go to the VBA IDE (alt-F11), insert a new module, and paste in this code:

All it does is put the formulas in for you. It assumes the named range UniqueNDCs scope is Workbook, if it is tied to a worksheet then it will have to be tweaked a bit. If you want hard values instead of the formulas, just add a line after the autofill:

rngStart.Offset(0, 1).Resize(lngRows, 1) = rngStart.Offset(0, 1).Resize(lngRows, 1).Value

You can associate this with a button on your sheet, I think you have that part well in hand. I assume you have a dynamic range for UniqueNDCs, something like:

=offset(Sheet1!A2,0,0,counta(Sheet1!A:A)-1,1)

So when you add/remove items the named range still reflects the correct range?

=IF(OFFSET(UniqueNDCs,ROW(1:1)-1,0)>0,OFFSET(UniqueNDCs,ROW(1:1)-1,0),"")

and

=SUMIF(NDCRange,A2,IASTQTY)

Since that isn't an option for you, a bit of VBA. Go to the VBA IDE (alt-F11), insert a new module, and paste in this code:

`Public`

`Sub`

`CalcTotalQty() `

` `

` `

`Dim`

`rngStart `

`As`

`Excel.Range `

` `

`Dim`

`lngRows `

`As`

`Long`

` `

` `

`Set`

`rngStart = ThisWorkbook.Names(`

`"UniqueNDCs"`

`).RefersToRange.Cells(1, 1) `

` `

`lngRows = ThisWorkbook.Names(`

`"UniqueNDCs"`

`).RefersToRange.Rows.Count `

` `

` `

`rngStart.Offset(0, 1).Resize(lngRows, 1).Formula = `

`"=SUMIF(NDCRange,"`

`& rngStart.Address(`

`False`

`, `

`True`

`) & `

`",IASTQTY)"`

` `

`Set`

`rngStart = `

`Nothing`

` `

`End`

`Sub`

All it does is put the formulas in for you. It assumes the named range UniqueNDCs scope is Workbook, if it is tied to a worksheet then it will have to be tweaked a bit. If you want hard values instead of the formulas, just add a line after the autofill:

rngStart.Offset(0, 1).Resize(lngRows, 1) = rngStart.Offset(0, 1).Resize(lngRows, 1).Value

You can associate this with a button on your sheet, I think you have that part well in hand. I assume you have a dynamic range for UniqueNDCs, something like:

=offset(Sheet1!A2,0,0,counta(Sheet1!A:A)-1,1)

So when you add/remove items the named range still reflects the correct range?

I would export the NDCRange to Access, run a query that sums the data and then reimport the results. The query to do the sum is very simple:

I would also consider using Access rather than Excel. It is better suited to your needs.

`Select`

`NDCRange, `

`Sum`

`(IASTQTY) `

`as`

`SumOfIASTQTY`

`From`

`MyTable`

`Group`

`By`

`NDCRange;`

I would also consider using Access rather than Excel. It is better suited to your needs.

Hi JD,

I give you here some idea for you to extend to your spreadsheet...

I separate your question into 2 steps, I assign the code to the buttons, on the last button call both steps to run...

I give you here some idea for you to extend to your spreadsheet...

I separate your question into 2 steps, I assign the code to the buttons, on the last button call both steps to run...

Try this...example attachment for you -->Vba_Sumif.zip

- Create unique product list from your product list, I create the unique list in column E...using this code

- Sub createUniqe()

Range("E2:F5000").ClearContents

Range("E1") = "UniqueNDCs"

LastProdRow = Range("A" & Rows.Count).End(xlUp).Row

For Each prod In Range("A2:A" & LastProdRow)

If WorksheetFunction.CountIf(Range("E:E"), prod) = 0 Then

Range("E" & Rows.Count).End(xlUp).Offset(1, 0) = prod.Value

End If

Next prod

Range("E2:E" & LastProdRow).Sort Key1:=Range("E2"), Order1:=xlAscending

MsgBox "Unique Product List was created successfully"

End Sub- In steps 2, we input the summation of each product in column F, using this code

- Sub sumQtyByProd()

Range("F1") = "Sum Qty By Product"

lastUnqRow = Range("E" & Rows.Count).End(xlUp).Row

For Each UnqProd In Range("E2:E" & lastUnqRow)

UnqProd.Offset(0, 1).Value = WorksheetFunction.SumIf(Range("A:A"), UnqProd, Range("B:B"))

Next UnqProd

MsgBox "Summary By Product is finished"

End Sub- Finally I group both code into one sub, using..

- Sub runAllCode()

Call createUniqe

Call sumQtyByProd

End Sub

Of course, this is just intend to give you some idea, then you can adjust to your own spreadsheet, if you face any difficulty or want to ask more, please feel free to raise your question again.

Hope this help.

Pichart Y.

hi,

Hope this will help you

As I know in Excel, the **SumIf** function adds all numbers in a range of cells, based on a given criteria.

The syntax for the **SumIf** function is:

SumIf( range, criteria, sum_range )

*range* is the range of cells that you want to apply the *criteria* against.

*criteria* is used to determine which cells to add.

*sum_range* are the cells to sum.

Applies To:

- Excel 2007, Excel 2003, Excel XP, Excel 2000

Hope this will help you

Thank you all for your responses! Sorry for the delay in responding back.

PiChartY,

I was able to adapt your code easily to the code I've already written and it works beautifully. I did have a question about the For Each UnqProd section of the code. How does VBA know what UnqProd is if you never declare that?

Thanks again for helping me out!

JD

PiChartY,

I was able to adapt your code easily to the code I've already written and it works beautifully. I did have a question about the For Each UnqProd section of the code. How does VBA know what UnqProd is if you never declare that?

Thanks again for helping me out!

JD

Hi JD,
You have same quest with me when I first use Vba.
Actually you can use any word instead of unqProd. Some one use just "X", "Y" or "a","b", etc.
Then how do vba know? Because when we use For each .....in range(.....) This is already declair that we want to get the things in that range. With the "Next UqdProd" below, tell vba to loop the second, third...till last thing in that range, finally exit loop.
The sequence of loop, always left to right then top to bottom of the range. If the range is "a1:a3" vba will go a1,a2 finally a3. If the range is "a1:c3", vba will go a1,b1,c1,then a2,b2 ... End at c3.
Hope this help
Any question you have, please feel free to ask again.
Pichart Y.