Sum Searched Criteria

Solved/Closed
-
 Jan -
This code works great at searching based on the userform criteria and then in the message it states the count of the rows pasted into the spreadsheet. Is there any way that the message can also give me the total of the amount that is pasted in the worksheet if the amount is in column L?


Sub CopyByCode()

Dim DestSheet As Worksheet
Dim NewRow As Integer

Set DestSheet = Worksheets("Itemized Costs")
NewRow = Worksheets("LISTS").Range("Z3").Value


Dim sRow As Long 'row index on source worksheet
Dim dRow As Long 'row index on destination worksheet
Dim sCount As Long


Dim strsearch As String
strsearch = CStr(UserForm2.CatName.Value)


For sRow = 1 To Range("D65536").End(xlUp).Row
If Cells(sRow, "U") Like strsearch Then
sCount = sCount + 1


dRow = NewRow + 1
NewRow = NewRow + 1

DestSheet.Cells(dRow, "B") = Cells(sRow, "B")
DestSheet.Cells(dRow, "C") = Cells(sRow, "C")
DestSheet.Cells(dRow, "D") = Cells(sRow, "D")
DestSheet.Cells(dRow, "E") = Cells(sRow, "E")
DestSheet.Cells(dRow, "F") = Cells(sRow, "F")
DestSheet.Cells(dRow, "G") = Cells(sRow, "G")
DestSheet.Cells(dRow, "H") = Cells(sRow, "H")
DestSheet.Cells(dRow, "I") = Cells(sRow, "I")
DestSheet.Cells(dRow, "J") = Cells(sRow, "J")
DestSheet.Cells(dRow, "K") = Cells(sRow, "K")
DestSheet.Cells(dRow, "L") = Cells(sRow, "L")


End If
Next sRow

1 reply

Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
769
You can add this line


SumL = SumL + Cells(sRow, "L")


So you new code would look like this


Sub CopyByCode()

Dim DestSheet As Worksheet
Dim NewRow As Integer

Set DestSheet = Worksheets("Itemized Costs")
NewRow = Worksheets("LISTS").Range("Z3").Value


Dim sRow As Long 'row index on source worksheet
Dim dRow As Long 'row index on destination worksheet
Dim sCount As Long
Dim SumL As Variant
SumL = 0

Dim strsearch As String
strsearch = CStr(UserForm2.CatName.Value)


For sRow = 1 To Range("D65536").End(xlUp).Row
If Cells(sRow, "U") Like strsearch Then
sCount = sCount + 1


dRow = NewRow + 1
NewRow = NewRow + 1

DestSheet.Cells(dRow, "B") = Cells(sRow, "B")
DestSheet.Cells(dRow, "C") = Cells(sRow, "C")
DestSheet.Cells(dRow, "D") = Cells(sRow, "D")
DestSheet.Cells(dRow, "E") = Cells(sRow, "E")
DestSheet.Cells(dRow, "F") = Cells(sRow, "F")
DestSheet.Cells(dRow, "G") = Cells(sRow, "G")
DestSheet.Cells(dRow, "H") = Cells(sRow, "H")
DestSheet.Cells(dRow, "I") = Cells(sRow, "I")
DestSheet.Cells(dRow, "J") = Cells(sRow, "J")
DestSheet.Cells(dRow, "K") = Cells(sRow, "K")
DestSheet.Cells(dRow, "L") = Cells(sRow, "L")

SumL = SumL + Cells(sRow, "L")

End If
Next sRow
This worked great!! Thank you! That was so easy.