One way...Have changed your approach...Assumes that Column always has either sales or income entries
Sub LBoxPop()
Dim Temp1, Temp2, Val1 As Double, Val2 As Double
With Sheets("SalesDB").Cells(1).CurrentRegion
Data = Filter(Evaluate("Transpose(If((" & .Columns(2).Address & "=""Client"")+(" & .Columns(2).Address & "=""" & Crit & """)*(" & .Columns(100).Address & "=""Sales""),row(1:" & .Rows.Count & ")))"), False, 0)
Temp1 = Application.Index(.Value, Application.Transpose(Data), Array(3, 4, 6, 7))
Val1 = Application.WorksheetFunction.SumIfs(.Columns(7), .Columns(2), Crit, .Columns(100), "Sales")
Data = Filter(Evaluate("Transpose(If((" & .Columns(2).Address & "=""Client"")+(" & .Columns(2).Address & "=""" & Crit & """)*(" & .Columns(100).Address & "=""Income""),row(1:" & .Rows.Count & ")))"), False, 0)
Temp2 = Application.Index(.Value, Application.Transpose(Data), Array(101, 102, 103, 104, 105))
Val2 = Application.WorksheetFunction.SumIfs(.Columns(102), .Columns(2), Crit, .Columns(100), "Income")
End With
With COLLECTIONS.SALESLB
.ColumnCount = 4
.ColumnWidths = "60;80;80;50"
.List = Temp1
.Parent.totalsales = Format(Val1, "0.00")
End With
With COLLECTIONS.PAYMENTRECEIVEDLB
.ColumnCount = 5
.ColumnWidths = "80;80;50;50,80"
.List = Temp2
.Parent.totalpaymentreceived = Format(Val2, "0.00")
End With
End Sub
Bookmarks