Beginner looping / summarize question

  • Thread starter Thread starter PGJ
  • Start date Start date
P

PGJ

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
 
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
 
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
 
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
 
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
 
Back
Top