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?

 

[FDibbins]

Well I just read that the SUMIF function reads the cell as string

You read that where, and which cell are you referring to?

What does your SUMIF() formula look like, and what is it referencing?

 

[ajay1]

Read it somewhere in the Forum.

SUM IF() formula is as follows following the TinyPic previously uploaded of my Spreadsheet


=SUMIF($B$6:$B$87,"<="&L7,$H$6:$H$87)

 

[FDibbins]

SUMIFS() doesn't "read" anything in any particular way, it just sees what is in the cell/s...if it is text, that is what it will see, if it is numeric, then it will see numbers. It will obviously not be able to sum text

What do you have in column B, column H and cell L7?

 

[ajay1]

SUMIFS() doesn't "read" anything in any particular way, it just sees what is in the cell/s...if it is text, that is what it will see, if it is numeric, then it will see numbers. It will obviously not be able to sum text

What do you have in column B, column H and cell L7?

In column B i have the Due Dates ordered ascending, and in Column H I have the Balances of each invoice. in L7 i entered a date. Ideally i want it to sum all invoices before or on that date.

 

[FDibbins]

(don't know where I got the sumifS() bit from lol)

=SUMIF($B$6:$B$87,"<="&L7,$H$6:$H$87)
Formula looks OK, what does that give you, and what did you expect?

 

[michaelsmith559]

This would be a starting point for the macro. I'm not sure of your sheet name, and I put a line in the code that says "change this line..." This is where you will put in your code for summing if less than date entered.

macro:

Sub Find_Date_and_Sum()
    Dim FindString As Date
    Dim Rng As Range
    FindString = InputBox("Enter Date")
    If Trim(FindString) <> "" Then
        With Sheets("Sheet1").Range("B:B")
            Set Rng = .Find(What:=FindString, _
                            After:=.Cells(.Cells.Count), _
                            LookIn:=xlValues, _
                            LookAt:=xlWhole, _
                            SearchOrder:=xlByRows, _
                            SearchDirection:=xlNext, _
                            MatchCase:=False)
            If Not Rng Is Nothing Then
                'change this line to what you want to sum
            Else
                MsgBox "Nothing found"
            End If
        End With
    End If
End Sub