Beginner looping / summarize question
 

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

Beginner looping / summarize question
 

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

Beginner looping / summarize question
 

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

Beginner looping / summarize question
 

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

Beginner looping / summarize question
 

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

Beginner looping / summarize question
 

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

Beginner looping / summarize question
 

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