Looping and Summing

[ajay1]

Trying to sum values in excel sheet based on due date. Goal is to create a macro where user has to press "Command Button" and Macro do the following:

1. Enter a date
2. Check the date against all the dates in Column B (AKA Due Date Column)
- IF the due date is earlier than the date entered, sum the amount due, else, move to the next cell
3. Return the value of the total sum

So far i have this:
Sub ARSUM()
Dim dateEntry As Date
dateEntry = InputBox(“Enter a Date”)
#Selection.Value = status
ARSUM(2).Range(“h3”).value = dateEntry

I know at this point I am going to need some kind of loop. Help?

 

[michaelsmith559]

Can you post a copy of your worksheet?

 

[ajay1]

Image - TinyPic - Free Image Hosting, Photo Sharing & Video Hosting

 

[michaelsmith559]

If I enter, 11/28/2014, what would be summed on your sheet? More specifically, what ranges need to be summed?

 

[mikerickson]

Have you looked at SUMIF?

 

[ajay1]

If I enter, 11/28/2014, what would be summed on your sheet? More specifically, what ranges need to be summed?

specifically, the Due date range will be checked for the date and the "Open Balance" column will be summed as well. I plan on the user using modifying these columns a good amount so i thought about, instead of ranging it from the beginning of the first balance to the last cell in the column, maybe it could look for the last cell that has data in it?

 

[ajay1]

Yes, I have looked at SUMIF. Unfortunately, when I try to format it for the date condition, it does not adjust every time I set a different date

 

[ajay1]

But Michaelsmith559, something along the lines I was thinking like
LR = Cells(Rows.Count, "A").End(xlup).Row

 

[FDibbins]

Yes, I have looked at SUMIF. Unfortunately, when I try to format it for the date condition, it does not adjust every time I set a different date

is the date you are entering a real date, or a text that looks like a dare? Test it with =ISNUMBER(cell-ref)

 

[ajay1]

Well I just read that the SUMIF function reads the cell as string, but what if I wanted to read it as a date?

Also, I tested the range, the test came out TRUE