Solved: Adding Calculation code

[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

[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.

[domfootwear]

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]

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,. :dunno .

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]

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]

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

[av8tordude]

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

[mdmackillop]

At the head of your code

Dte = DateValue(txtDate2)
Dte1 = DateValue(Range("C11"))

If Dte1 > Dte Then
Cells.Interior.ColorIndex = xlNone
Me.SME.Caption = Format(0, "$##,###0.00")
Me.TME.Caption = Format(0, "$##,###0.00")
Me.EAR1.Caption = Format(0, "$##,###0.00")
Me.EAR2.Caption = Format(0, "$##,###0.00")
Exit Sub
End If

[av8tordude]

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.

[mdmackillop]

Try this update