How do you set up code that loops through a range summarizing items that
meet a certain criteria?

For example, If the date in column A = 01-31-05 then add up the amounts.

Column A Colume B
01-31-05 50.00
01-31-05 100.00
02-01-05 200.00

desired result would be 150.00.

What would the code look like?

Thanks in advance

sub countdates()
sumamt=0
for i=1 to 3
if cdate(cells(i,1))=cdate(1/31/05) then 'assuming row starts w/ 1
sumamt=sumamt+cells(i,2)
else
end if
next i
end sub
"PGJ" wrote in message
...
How do you set up code that loops through a range summarizing items that
meet a certain criteria?

For example, If the date in column A = 01-31-05 then add up the amounts.

Column A Colume B
01-31-05 50.00
01-31-05 100.00
02-01-05 200.00

desired result would be 150.00.

What would the code look like?

Thanks in advance

There are lots of ways to skin a cat. Here is what I might call the beginner
way as it is probably easiest to understand.

function AddStuff() as double
dim dblReturnValue as double

sheets("Sheet1").range("A2").select
do while activecell.value < ""
dblReturnvalue = dblreturnvalue + activecell.offset(0,1).value
activecell.offset(1,0).select
loop

addstuff = dblreturnvalue
end function

Here it is a little more advanced using range objects. I am not moving the
active cell which can be very advantageous...

function AddStuff() as double
dim dblReturnValue as double
dim rngCell as range
dim rngAllPeriods

set rngAllPeriods = range(sheet1.range("A1"), sheet1.Range("A1").end
xlDown)).offset(0,1)

for each rngcell in rngallperiods
dblreturnvalue = dblreturnvalue = rngCell.value
next rngcell

addstuff = dblreturnvalue
end function

And without a loop...

function AddStuff() as double

addstuff = application.sum(range(sheet1.range("A1"), sheet1.Range("A1").end
xlDown)).offset(0,1))

end function

HTH

"PGJ" wrote:

How do you set up code that loops through a range summarizing items that
meet a certain criteria?

For example, If the date in column A = 01-31-05 then add up the amounts.

Column A Colume B
01-31-05 50.00
01-31-05 100.00
02-01-05 200.00

desired result would be 150.00.

What would the code look like?

Thanks in advance

Sorry. My last post was kinda wrong. I didn't see your criteria.

"PGJ" wrote:

How do you set up code that loops through a range summarizing items that
meet a certain criteria?

For example, If the date in column A = 01-31-05 then add up the amounts.

Column A Colume B
01-31-05 50.00
01-31-05 100.00
02-01-05 200.00

desired result would be 150.00.

What would the code look like?

Thanks in advance

Here it is fixed up...
function AddStuff() as double
dim dblReturnValue as double

sheets("Sheet1").range("A2").select
do while activecell.value < ""
if activecell.value = cdate("1/31/05")
dblReturnvalue = dblreturnvalue + activecell.offset(0,1).value
endif
activecell.offset(1,0).select
loop

addstuff = dblreturnvalue
end function

Here it is a little more advanced using range objects. I am not moving the
active cell which can be very advantageous...

function AddStuff() as double
dim dblReturnValue as double
dim rngCell as range
dim rngAllPeriods

set rngAllPeriods = range(sheet1.range("A1"), sheet1.Range("A1").end
xlDown))

for each rngcell in rngallperiods
if rngCell.value = cdate("1/31/05")_
dblreturnvalue = dblreturnvalue + rngCell.offset(0,1)value
next rngcell

addstuff = dblreturnvalue
end function

"Jim Thomlinson" wrote:

There are lots of ways to skin a cat. Here is what I might call the beginner
way as it is probably easiest to understand.
function AddStuff() as double
dim dblReturnValue as double

sheets("Sheet1").range("A2").select
do while activecell.value < ""
dblReturnvalue = dblreturnvalue + activecell.offset(0,1).value
activecell.offset(1,0).select
loop

addstuff = dblreturnvalue
end function

Here it is a little more advanced using range objects. I am not moving the
active cell which can be very advantageous...

function AddStuff() as double
dim dblReturnValue as double
dim rngCell as range
dim rngAllPeriods

set rngAllPeriods = range(sheet1.range("A1"), sheet1.Range("A1").end
xlDown)).offset(0,1)

for each rngcell in rngallperiods
dblreturnvalue = dblreturnvalue = rngCell.value
next rngcell

addstuff = dblreturnvalue
end function


And without a loop...

function AddStuff() as double

addstuff = application.sum(range(sheet1.range("A1"), sheet1.Range("A1").end
xlDown)).offset(0,1))

end function

HTH

"PGJ" wrote:

How do you set up code that loops through a range summarizing items that
meet a certain criteria?

For example, If the date in column A = 01-31-05 then add up the amounts.

Column A Colume B
01-31-05 50.00
01-31-05 100.00
02-01-05 200.00

desired result would be 150.00.

What would the code look like?

Thanks in advance

my previous was a bit assumptive. this will work. put the 1/31/05 in C1 in
this example and answer will go in D1.
Sub countdates()
Dim yourdate As Date
yourdate = Range("c1")

sumamt = 0
For i = 1 To 3
If Cells(i, 1) = yourdate Then 'assuming row starts w/ 1
sumamt = sumamt + Cells(i, 2)
Else
End If
Next i
Cells("d" & i) = sumamt
End Sub


"mike allen" wrote in message
...
sub countdates()
sumamt=0
for i=1 to 3
if cdate(cells(i,1))=cdate(1/31/05) then 'assuming row starts w/ 1
sumamt=sumamt+cells(i,2)
else
end if
next i
end sub
"PGJ" wrote in message
...
How do you set up code that loops through a range summarizing items that
meet a certain criteria?

For example, If the date in column A = 01-31-05 then add up the amounts.

Column A Colume B
01-31-05 50.00
01-31-05 100.00
02-01-05 200.00

desired result would be 150.00.

What would the code look like?

Thanks in advance

alright, alright, i slipped again. is there any reason a ask more than
answer? this is what i meant:
Sub countdates()
Dim yourdate As Date
yourdate = Range("c1")
sumamt = 0
For i = 1 To 3
If Cells(i, 1) = yourdate Then 'assuming row starts w/ 1
sumamt = sumamt + Cells(i, 2)
Else
End If
Next i
Range("d1") = sumamt
End Sub




"mike allen" wrote in message
...
sub countdates()
sumamt=0
for i=1 to 3
if cdate(cells(i,1))=cdate(1/31/05) then 'assuming row starts w/ 1
sumamt=sumamt+cells(i,2)
else
end if
next i
end sub
"PGJ" wrote in message
...
How do you set up code that loops through a range summarizing items that
meet a certain criteria?

For example, If the date in column A = 01-31-05 then add up the amounts.

Column A Colume B
01-31-05 50.00
01-31-05 100.00
02-01-05 200.00

desired result would be 150.00.

What would the code look like?

Thanks in advance