I'm looking to create a sub that will pull information from a
different sheet ("sheet2"), and sum it into a cell on "sheet1". Sheet
2 contains a list of product #'s in column B, and I want it to
basically act as a function "SUMIF", where it sums all the costs
(column F) associated with that product if that correct product code
is in column B.

I know I can do this via a function (SUMIF), but I'm trying to learn
VBA, and thought I might as well start now.

Thanks!

Hi,

For what you are trying to do you'll probably find it is simplest to use the
same function but within VBA.

Say you have your products listed in cells A1-A8, the costs are in B1-B8,
and you want to use SumIf to some the costs for products that match a product
listed in cell E1. You want the sum to be in cell D1.

In the spreadsheet you would type directly in cell D1:
=SUMIF(A1:A8,E1,B1:B8)

Whereas in VBA you could type:
Cells(1, 4) = Application.WorksheetFunction.SumIf(Range(Cells(1, 1),
Cells(8, 1)), Cells(1, 5), Range(Cells(1, 2), Cells(8, 2)))

You can place Worksheets("Sheet1") infront of each Cells() reference if you
wish to use ranges or results that are on different (or specific) sheets.

It is often simpler and faster to use a worksheet function in this way,
where it is available.

An alternative way to do this would be to create a variable to store your
sum, go to check each cell one by one in your products range, and sum the
value on that row in the costs range, and finally placing the result in your
destination cell:

Public Sub My_SumIf()
Dim rngProducts As Range
Dim rngCells As Range
Dim intCostsColumn As Integer
Dim dblSumIfTotal As Double
Dim strSearchProduct As String
'SET PRODUCT RANGE
Set rngProducts = Worksheets("Sheet1").Range(Cells(1, 1), Cells(8, 1))
'SET COST COLUMN
intCostsColumn = 2
'INITIALISE TOTAL (NOT ALWAYS NECESSARY, BUT CAN BE GOOD PRACTICE)
dblSumIfTotal = 0
'GET THE PRODUCT I WANT TO SUM FOR
strSearchProduct = Worksheets("Sheet1").Cells(1, 5)
'LOOP THROUGH ALL CELLS IN MY PRODUCT RANGE
For Each rngCells In rngProducts
'IS THE PRODUCT TO BE SUMMED
If rngCells.Value = strSearchProduct Then
'CHECK FOR NUMERIC VALUE IN COSTS
If IsNumeric(Worksheets("Sheet1").Cells(rngCells.Row, intCostsColumn)) Then
'ADD TO RUNNING TOTAL
dblSumIfTotal = dblSumIfTotal + Worksheets("Sheet1").Cells(rngCells.Row,
intCostsColumn)
Else
MsgBox ("Non numeric found in costs")
End If
End If
Next
'PLACE RESULT IN MY RESULT CELL
Worksheets("Sheet1").Cells(1, 4) = dblSumIfTotal
End Sub

So there's some code to get you started anyway, but as I say, the worksheet
functions will generally be quicker than trying to rewrite them in VBA code.

Hope this helps,

Sean.


--
(please remember to click yes if replies you receive are helpful to you)


" wrote:

I'm looking to create a sub that will pull information from a
different sheet ("sheet2"), and sum it into a cell on "sheet1". Sheet
2 contains a list of product #'s in column B, and I want it to
basically act as a function "SUMIF", where it sums all the costs
(column F) associated with that product if that correct product code
is in column B.

I know I can do this via a function (SUMIF), but I'm trying to learn
VBA, and thought I might as well start now.

Thanks!

Anyhelp would be greatly appreciated. Thanks.

dblSumIfTotal = dblSumIfTotal + Worksheets("Sheet1").Cells(rngCells.Row,
intCostsColumn)

Everything seems to work, except the statement above. Any ideas?