200+ Video Lessons
50+ Hours of Video
200+ Excel Guides
Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)
Hello,
I would achieve my project by sum columns 3,4 in listbox on userform and show TOTAL row in last row in listbox1 when show data on form whether run form or use combobox1 or textboxes , as to column 5 will keep without sum just subtract column 3 from 4 in TOTAL row in listbox1 . I would calculate on userform when show data.
I don't prefer using textboxes for calculation.
thanks for any help.
Hi again Malkal
Sorry but I only just saw this (TeachExcel is very quiet these days and I don't get notifications).
I think I understand what you want so have modified the code (and tidied up your UserForm1) in the attached revised file.
In the UserForm1 procedure Sub FilterData(), I added a new variable (bold in the extract below):
' Add new variable F2Total and set to 0
Dim FTotal As Double, F2Total As Double
FTotal = 0
F2Total = 0
That's used in the changes to this section:
For i = 1 To UBound(a, 1)
If (ComboBox2.Value = "" Or LCase(a(i, 2)) Like "*" & LCase(ComboBox2.Text) & "*") And _
(IsDate(a(i, 1)) And a(i, 1) >= dateFrom And a(i, 1) <= dateTo) Then
k = k + 1
ReDim Preserve b(1 To UBound(a, 2), 1 To k)
For j = 1 To UBound(a, 2)
b(j, k) = Format(a(i, j), "DD/MM/YYYY")
Next
' Add individual totals...
If k <= 1 Then
If IsNumeric(a(i, 3)) Then FTotal = a(i, 3)
If IsNumeric(a(i, 4)) Then F2Total = a(i, 4)
Else
If IsNumeric(a(i, 3)) Then FTotal = FTotal + a(i, 3)
If IsNumeric(a(i, 4)) Then F2Total = F2Total + a(i, 4)
End If
Those new totals are used in the revisions here:
'If ComboBox2 <> "" And ComboBox2 = "" And k > 0 Then
' ...add the total row
ListBox1.AddItem
'If k = 1 Then
' pick title, dependent on any values
If (ComboBox2 <> "" Or TextBox4 <> "" Or TextBox5 <> "") And k > 0 Then
ListBox1.List(k, 0) = "Filtered totals"
Else
ListBox1.List(k, 0) = "Totals"
End If
' populate columns with totals
ListBox1.List(k, 1) = ">>>>>>>>>>>>>>>>>>>>>>>"
ListBox1.List(k, 2) = FTotal
ListBox1.List(k, 3) = F2Total
ListBox1.List(k, 4) = FTotal - F2Total
' End If
The totals row should be obvious (and say "Filtered..." if you sets any values).
Note that I corrected the spelling of "openning" (to "opening") in both the sheet and code. Also that your button may need reassigned (or you could use Userform1.Show in the Immediate window)
Hope this fixes your problem. If so please mark this Answer as Selected.