Sum Searched Criteria
Solved/Closed
Related:
- Sum Searched Criteria
- Recently searched websites - Guide
- How to sum days in excel - Guide
- Excel sum in french - Guide
- Excel sum if another cell contains - Excel Forum
- The following excel image has a vba program (see below). what will be the result after the program is executed? any cells not displayed are currently empty, and any cells with numbers are formatted numeric. sub afdo a=0 lastrow = cels(rows. count, 1). end(xdup). row for i =14 to lastrow step 2 a=a cellsii. 5) value next cells(lastrow 2, 1) value = "the sum - Guide
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
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
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
Apr 14, 2010 at 03:06 PM