Loop through Dates for a value

[zach454]

I am having a problem I can't figure out. I have every day in a date format in Column B which I'm wanting to ONLY search for the current month and grab the values in Column F and add them, giving me a monthly total. I have this loop which only works if the 1st row is this current month. I can't see to figure this out? Any suggestions? Thanks!

c = Month(Date)

For Each c In DateRng
j = Range("F" & k).Offset(i, 0)
i = i + 1
sum = sum + j
Debug.Print sum
If Month(c) <> Month(Date) Then Exit For
Next c

 

[Nalani]

I'm not sure what your macro is doing being that it is just part of the whole Macro.

You could accomplish the same without a Macro by using a Formula. Look at the table below, I put the formula in to Sum 2 columns in case you may(or want) the same.

Excel Workbook
	A	B	C	D	E	F
1	Month to Search		Mar	Totals	$345.00	$2,445.00
2		DATE			Value1	Value2
3		2/9/2011			22	190
4		2/9/2011			183	378
5		2/9/2011			454	249
6		3/1/2011			66	200
7		3/30/2011			117	2083
8		3/30/2011			162	162
9		4/5/2011			236	684
10		4/5/2011			58	237
11		4/15/2011			23	114
12		4/25/2011			841	327
13		4/25/2011			191	1635
Sheet1

Excel 2007

Cell Formulas
Range	Formula
E1	=SUMPRODUCT(--(TEXT($B$3:$B$30,"mmm")=$C$1),E$3:E$30)
F1	=SUMPRODUCT(--(TEXT($B$3:$B$30,"mmm")=$C$1),F$3:F$30)

By changeing C1 to the month (i.e Feb or Apr) it will Sum based off of that month.

 

[Ruddles]

You're stopping as soon as you find a date which is the current month. You need to complete the loop and only add cells which match the current month.

Maybe:-

c = Month(Date) [COLOR=green]' this isn't doing anything as you are overwriting c immediately[/COLOR]

For Each c In DateRng
   If Month(c) = Month(Date) Then 
      j = Range("F" & k).Offset(i, 0)
      i = i + 1
      sum = sum + j
      Debug.Print sum
   Endif
Next c