Sum Searched Criteria

Solved/Closed
Jan - Apr 13, 2010 at 11:27 PM
 Jan - Apr 14, 2010 at 03:06 PM
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 response

rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 766
Apr 14, 2010 at 11:11 AM
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
0
This worked great!! Thank you! That was so easy.
0