Solved: Adding Calculation code

**av8tordude** · 03-11-2010, 06:33 PM

Can some assist me in the below request....

1. I inserted a Date Picker and two arrow cmd buttons. Instead of the code calculating the total of what is in the table (C11:J375), I would like to calculate the total only up to and including the date selected.

Example: If 03/07/10 is selected, then the calculation should only include dates from the first entry to 03/07/10. Data from 03/08/10 should not be included in the calculation.

2. When clicking either arrow button and a new date is entered in either direction, the calculations should be updated.

Example: If 03/07/10 is displayed and I click the right arrow, 03/08/10 should be displayed and the calculation should be updated.

Thank you for your help

**lucas** · 03-11-2010, 07:16 PM

I got a control not available on this machine error which is a common problem with the calendar control.

I also tried the left and right buttons but it errored because the textboxes were not on the userform.

**domfootwear** · 03-11-2010, 07:24 PM

Originally Posted by av8tordude

Can some assist me in the below request....

1. I inserted a Date Picker and two arrow cmd buttons. Instead of the code calculating the total of what is in the table (C11:J375), I would like to calculate the total only up to and including the date selected.

Example: If 03/07/10 is selected, then the calculation should only include dates from the first entry to 03/07/10. Data from 03/08/10 should not be included in the calculation.

2. When clicking either arrow button and a new date is entered in either direction, the calculations should be updated.

Example: If 03/07/10 is displayed and I click the right arrow, 03/08/10 should be displayed and the calculation should be updated.

Thank you for your help

You can use Sumif function for this question
Use this code:

Private Sub nxt_Click()
txtDate2.Value = txtDate2.Value + 1
With Application.WorksheetFunction
    Me.SME.Caption = Format(.SumIf(Range("c11:c375"), txtDate2, Range("i11:i375")), "$##,###0.00")
    Me.TME.Caption = Format(.SumIf(Range("c11:c375"), txtDate2, Range("J11:J375")), "$##,###0.00")
    Me.EAR1.Caption = Format(.SumIf(Range("c11:c375"), txtDate2, Range("G11:G375")), "$##,###0.00")
    Me.EAR2.Caption = Format(.SumIf(Range("c11:c375"), txtDate2, Range("G11:G375")), "$##,###0.00")
    Me.NME1.Caption = Format(Val(Replace(Replace(Me.SME.Caption, "$", ""), ",", "")) - Val(Replace(Replace(Me.EAR1.Caption, "$", ""), ",", "")), "$##,###0.00")
    Me.NME2.Caption = Format(Val(Replace(Replace(Me.TME.Caption, "$", ""), ",", "")) - Val(Replace(Replace(Me.EAR2.Caption, "$", ""), ",", "")), "$##,###0.00")
    Me.TDME1.Caption = Format(Val(Range("AJ11").Value) * Val(Replace(Replace(Me.NME1.Caption, "$", ""), ",", "")), "$##,###0.00")
    Me.TDME2.Caption = Format(Val(Range("AJ11").Value) * Val(Replace(Replace(Me.NME2.Caption, "$", ""), ",", "")), "$##,###0.00")
End With
End Sub

'......................................

Private Sub Prev_Click()
txtDate2.Value = txtDate2.Value - 1
With Application.WorksheetFunction
    Me.SME.Caption = Format(.SumIf(Range("c11:c375"), txtDate2, Range("i11:i375")), "$##,###0.00")
    Me.TME.Caption = Format(.SumIf(Range("c11:c375"), txtDate2, Range("J11:J375")), "$##,###0.00")
    Me.EAR1.Caption = Format(.SumIf(Range("c11:c375"), txtDate2, Range("G11:G375")), "$##,###0.00")
    Me.EAR2.Caption = Format(.SumIf(Range("c11:c375"), txtDate2, Range("G11:G375")), "$##,###0.00")
    Me.NME1.Caption = Format(Val(Replace(Replace(Me.SME.Caption, "$", ""), ",", "")) - Val(Replace(Replace(Me.EAR1.Caption, "$", ""), ",", "")), "$##,###0.00")
    Me.NME2.Caption = Format(Val(Replace(Replace(Me.TME.Caption, "$", ""), ",", "")) - Val(Replace(Replace(Me.EAR2.Caption, "$", ""), ",", "")), "$##,###0.00")
    Me.TDME1.Caption = Format(Val(Range("AJ11").Value) * Val(Replace(Replace(Me.NME1.Caption, "$", ""), ",", "")), "$##,###0.00")
    Me.TDME2.Caption = Format(Val(Range("AJ11").Value) * Val(Replace(Replace(Me.NME2.Caption, "$", ""), ",", "")), "$##,###0.00")
End With

End Sub

**av8tordude** · 03-11-2010, 07:31 PM

Originally Posted by lucas

I got a control not available on this machine error which is a common problem with the calendar control.

I also tried the left and right buttons but it errored because the textboxes were not on the userform.

I'm not sure why you would be getting an error as the date picker should be included with your software,.

.

As for txtboxes, I labeled the datepicker "txtDate2" for consistency with my other code in my workbook.

Was my request not understandable? I could try to re-word it...

**av8tordude** · 03-11-2010, 07:40 PM

Originally Posted by domfootwear

You can use Sumif function for this question
Use this code:

Private Sub nxt_Click()
txtDate2.Value = txtDate2.Value + 1
With Application.WorksheetFunction
    Me.SME.Caption = Format(.SumIf(Range("c11:c375"), txtDate2, Range("i11:i375")), "$##,###0.00")
    Me.TME.Caption = Format(.SumIf(Range("c11:c375"), txtDate2, Range("J11:J375")), "$##,###0.00")
    Me.EAR1.Caption = Format(.SumIf(Range("c11:c375"), txtDate2, Range("G11:G375")), "$##,###0.00")
    Me.EAR2.Caption = Format(.SumIf(Range("c11:c375"), txtDate2, Range("G11:G375")), "$##,###0.00")
    Me.NME1.Caption = Format(Val(Replace(Replace(Me.SME.Caption, "$", ""), ",", "")) - Val(Replace(Replace(Me.EAR1.Caption, "$", ""), ",", "")), "$##,###0.00")
    Me.NME2.Caption = Format(Val(Replace(Replace(Me.TME.Caption, "$", ""), ",", "")) - Val(Replace(Replace(Me.EAR2.Caption, "$", ""), ",", "")), "$##,###0.00")
    Me.TDME1.Caption = Format(Val(Range("AJ11").Value) * Val(Replace(Replace(Me.NME1.Caption, "$", ""), ",", "")), "$##,###0.00")
    Me.TDME2.Caption = Format(Val(Range("AJ11").Value) * Val(Replace(Replace(Me.NME2.Caption, "$", ""), ",", "")), "$##,###0.00")
End With
End Sub
 
'......................................
 
Private Sub Prev_Click()
txtDate2.Value = txtDate2.Value - 1
With Application.WorksheetFunction
    Me.SME.Caption = Format(.SumIf(Range("c11:c375"), txtDate2, Range("i11:i375")), "$##,###0.00")
    Me.TME.Caption = Format(.SumIf(Range("c11:c375"), txtDate2, Range("J11:J375")), "$##,###0.00")
    Me.EAR1.Caption = Format(.SumIf(Range("c11:c375"), txtDate2, Range("G11:G375")), "$##,###0.00")
    Me.EAR2.Caption = Format(.SumIf(Range("c11:c375"), txtDate2, Range("G11:G375")), "$##,###0.00")
    Me.NME1.Caption = Format(Val(Replace(Replace(Me.SME.Caption, "$", ""), ",", "")) - Val(Replace(Replace(Me.EAR1.Caption, "$", ""), ",", "")), "$##,###0.00")
    Me.NME2.Caption = Format(Val(Replace(Replace(Me.TME.Caption, "$", ""), ",", "")) - Val(Replace(Replace(Me.EAR2.Caption, "$", ""), ",", "")), "$##,###0.00")
    Me.TDME1.Caption = Format(Val(Range("AJ11").Value) * Val(Replace(Replace(Me.NME1.Caption, "$", ""), ",", "")), "$##,###0.00")
    Me.TDME2.Caption = Format(Val(Range("AJ11").Value) * Val(Replace(Replace(Me.NME2.Caption, "$", ""), ",", "")), "$##,###0.00")
End With
 
End Sub

Hi Dom

I tried your piece, but it only displays the calculation for each date. It doesn't accomplish what I requested. could you elaborate more? Thanks

**mdmackillop** · 03-12-2010, 05:51 AM

Try this out. You may need to change the date format in the Find line to suit Regional Settings.

**av8tordude** · 03-12-2010, 07:20 PM

Originally Posted by mdmackillop

Try this out. You may need to change the date format in the Find line to suit Regional Settings.

Thanks mdmackillop. I had to rework the code to do exactly what I wanted to accomplish but your code provided a conduit to what i needed.

I was wondering, since C11 is the first entry in the table, is it possible to show $0.00 amounts if the date selected is prior to the first dated entry?

Example:

3/1/10 is the first entry. If I select a date prior to 3/1, then the amounts would disply as $0.00

Sub GetData()
    Dim Dte As Long
    Dim c As Range
    Dim Rw As Long
    Dte = DateValue(txtDate2)
    Set c = Columns(3).Find(Format(Dte, "mm/dd/yy"), LookIn:=xlValues, LookAt:=xlPart)
    If Not c Is Nothing Then
        Rw = c.Row - 10
        With Application.WorksheetFunction
            Me.SME.Caption = Format(.Sum(Range("C11").Offset(, 6).Resize(Rw)), "$##,###0.00")
            Me.TME.Caption = Format(.Sum(Range("C11").Offset(, 7).Resize(Rw)), "$##,###0.00")
            Me.EAR1.Caption = Format(.Sum(Range("C11").Offset(, 4).Resize(Rw)), "$##,###0.00")
            Me.EAR2.Caption = Format(.Sum(Range("C11").Offset(, 4).Resize(Rw)), "$##,###0.00")
            Me.NME1.Caption = Format(Val(Replace(Replace(Me.SME.Caption, "$", ""), ",", "")) - Val(Replace(Replace(Me.EAR1.Caption, "$", ""), ",", "")), "$##,###0.00")
            Me.NME2.Caption = Format(Val(Replace(Replace(Me.TME.Caption, "$", ""), ",", "")) - Val(Replace(Replace(Me.EAR2.Caption, "$", ""), ",", "")), "$##,###0.00")
            Me.TDME1.Caption = Format(Val(Range("AJ11").Value) * Val(Replace(Replace(Me.NME1.Caption, "$", ""), ",", "")), "$##,###0.00")
            Me.TDME2.Caption = Format(Val(Range("AJ11").Value) * Val(Replace(Replace(Me.NME2.Caption, "$", ""), ",", "")), "$##,###0.00")
        End With
    'debug check
    Cells.Interior.ColorIndex = xlNone
    Range("C11").Offset(, 6).Resize(Rw).Interior.ColorIndex = 6
    Range("C11").Offset(, 4).Resize(Rw).Interior.ColorIndex = 4
    Range("C11").Offset(, 7).Resize(Rw).Interior.ColorIndex = 7
    End If
End Sub

**av8tordude** · 03-17-2010, 01:09 PM

Hi everyone,

I notice that If i chose a date from txtDate2 date-picker that did not have an existing dated entry in the table, the amounts do not update.

For example...

Select 3/03/10 from the date-picker...the amounts get updated
Select 3/17/10 from the date-picker...the amounts are not updating.

I'm not sure if it needs a range, but if so, the range should be from C11:J375.

Can someone help...Thanks in advance.

Thread: Solved: Adding Calculation code

Thread Tools

Display

Solved: Adding Calculation code

Posting Permissions