V
Vacuum Sealed
Hi All
Hoping everyone had a great holiday season.
For all intensive purposes, the code below looks fat and long-winded yet
works well. That said! I was thinking it could lose a little weight and
run somewhat faster....
I could use Copy/Paste, but I am trying to stay away from it and learn
to do it better
Any assistance is appreciated.
Dim AWS As Worksheet
Dim SWS As Worksheet
Dim Tenders As Range
Dim tVal1 As Range, tVal2 As Range, tVal3 As Range, tVal4 As Range,
tVal5 As Range, tVal6 As Range
Dim tVal7 As Range, tVal8 As Range, tVal9 As Range, tVal10 As Range,
tVal11 As Range, tVal12 As Range
Dim tVal13 As Range, tVal14 As Range, tVal15 As Range, tVal16 As Range,
tVal17 As Range, tVal18 As Range
Dim tVal19 As Range, tVal20 As Range, tVal21 As Range, tVal22 As Range,
tVal23 As Range, tVal24 As Range
Set AWS = ActiveSheet
Set SWS = Worksheets("TMS DATA")
Set Tenders = SWS.Range("N6:N200")
Set tVal1 = [G12]
Set tVal2 = [H12]
Set tVal3 = [I12]
Set tVal4 = [J12]
Set tVal5 = [K12]
Set tVal6 = [L12]
Set tVal7 = [M12]
Set tVal8 = [N12]
Set tVal9 = [O12]
Set tVal10 = [P12]
Set tVal11 = [Q12]
Set tVal12 = [R12]
Set tVal13 = [S12]
Set tVal14 = [T12]
Set tVal15 = [U12]
Set tVal16 = [V12]
Set tVal17 = [W12]
Set tVal18 = [X12]
Set tVal19 = [Y12]
Set tVal20 = [Z12]
Set tVal21 = [AA12]
Set tVal22 = [AB12]
Set tVal23 = [AC12]
Set tVal24 = [AD12]
Range("G13").Select
With ActiveCell
.Value = Application.WorksheetFunction.CountIf(Tenders, tVal1)
.Offset(0, 1).Select
End With
With ActiveCell
.Value = Application.WorksheetFunction.CountIf(Tenders, tVal2)
.Offset(0, 1).Select
End With
With ActiveCell
.Value = Application.WorksheetFunction.CountIf(Tenders, tVal3)
.Offset(0, 1).Select
End With
With ActiveCell
.Value = Application.WorksheetFunction.CountIf(Tenders, tVal4)
.Offset(0, 1).Select
End With
With ActiveCell
.Value = Application.WorksheetFunction.CountIf(Tenders, tVal5)
.Offset(0, 1).Select
End With
With ActiveCell
.Value = Application.WorksheetFunction.CountIf(Tenders, tVal6)
.Offset(0, 1).Select
End With
With ActiveCell
.Value = Application.WorksheetFunction.CountIf(Tenders, tVal7)
.Offset(0, 1).Select
End With
With ActiveCell
.Value = Application.WorksheetFunction.CountIf(Tenders, tVal8)
.Offset(0, 1).Select
End With
With ActiveCell
.Value = Application.WorksheetFunction.CountIf(Tenders, tVal9)
.Offset(0, 1).Select
End With
With ActiveCell
.Value = Application.WorksheetFunction.CountIf(Tenders, tVal10)
.Offset(0, 1).Select
End With
With ActiveCell
.Value = Application.WorksheetFunction.CountIf(Tenders, tVal11)
.Offset(0, 1).Select
End With
With ActiveCell
.Value = Application.WorksheetFunction.CountIf(Tenders, tVal12)
.Offset(0, 1).Select
End With
With ActiveCell
.Value = Application.WorksheetFunction.CountIf(Tenders, tVal13)
.Offset(0, 1).Select
End With
With ActiveCell
.Value = Application.WorksheetFunction.CountIf(Tenders, tVal14)
.Offset(0, 1).Select
End With
With ActiveCell
.Value = Application.WorksheetFunction.CountIf(Tenders, tVal15)
.Offset(0, 1).Select
End With
With ActiveCell
.Value = Application.WorksheetFunction.CountIf(Tenders, tVal16)
.Offset(0, 1).Select
End With
With ActiveCell
.Value = Application.WorksheetFunction.CountIf(Tenders, tVal17)
.Offset(0, 1).Select
End With
With ActiveCell
.Value = Application.WorksheetFunction.CountIf(Tenders, tVal18)
.Offset(0, 1).Select
End With
With ActiveCell
.Value = Application.WorksheetFunction.CountIf(Tenders, tVal19)
.Offset(0, 1).Select
End With
With ActiveCell
.Value = Application.WorksheetFunction.CountIf(Tenders, tVal20)
.Offset(0, 1).Select
End With
With ActiveCell
.Value = Application.WorksheetFunction.CountIf(Tenders, tVal21)
.Offset(0, 1).Select
End With
With ActiveCell
.Value = Application.WorksheetFunction.CountIf(Tenders, tVal22)
.Offset(0, 1).Select
End With
With ActiveCell
.Value = Application.WorksheetFunction.CountIf(Tenders, tVal23)
.Offset(0, 1).Select
End With
With ActiveCell
.Value = Application.WorksheetFunction.CountIf(Tenders, tVal24)
End With
End Sub
TIA
Mick
Hoping everyone had a great holiday season.
For all intensive purposes, the code below looks fat and long-winded yet
works well. That said! I was thinking it could lose a little weight and
run somewhat faster....
I could use Copy/Paste, but I am trying to stay away from it and learn
to do it better
Any assistance is appreciated.
Dim AWS As Worksheet
Dim SWS As Worksheet
Dim Tenders As Range
Dim tVal1 As Range, tVal2 As Range, tVal3 As Range, tVal4 As Range,
tVal5 As Range, tVal6 As Range
Dim tVal7 As Range, tVal8 As Range, tVal9 As Range, tVal10 As Range,
tVal11 As Range, tVal12 As Range
Dim tVal13 As Range, tVal14 As Range, tVal15 As Range, tVal16 As Range,
tVal17 As Range, tVal18 As Range
Dim tVal19 As Range, tVal20 As Range, tVal21 As Range, tVal22 As Range,
tVal23 As Range, tVal24 As Range
Set AWS = ActiveSheet
Set SWS = Worksheets("TMS DATA")
Set Tenders = SWS.Range("N6:N200")
Set tVal1 = [G12]
Set tVal2 = [H12]
Set tVal3 = [I12]
Set tVal4 = [J12]
Set tVal5 = [K12]
Set tVal6 = [L12]
Set tVal7 = [M12]
Set tVal8 = [N12]
Set tVal9 = [O12]
Set tVal10 = [P12]
Set tVal11 = [Q12]
Set tVal12 = [R12]
Set tVal13 = [S12]
Set tVal14 = [T12]
Set tVal15 = [U12]
Set tVal16 = [V12]
Set tVal17 = [W12]
Set tVal18 = [X12]
Set tVal19 = [Y12]
Set tVal20 = [Z12]
Set tVal21 = [AA12]
Set tVal22 = [AB12]
Set tVal23 = [AC12]
Set tVal24 = [AD12]
Range("G13").Select
With ActiveCell
.Value = Application.WorksheetFunction.CountIf(Tenders, tVal1)
.Offset(0, 1).Select
End With
With ActiveCell
.Value = Application.WorksheetFunction.CountIf(Tenders, tVal2)
.Offset(0, 1).Select
End With
With ActiveCell
.Value = Application.WorksheetFunction.CountIf(Tenders, tVal3)
.Offset(0, 1).Select
End With
With ActiveCell
.Value = Application.WorksheetFunction.CountIf(Tenders, tVal4)
.Offset(0, 1).Select
End With
With ActiveCell
.Value = Application.WorksheetFunction.CountIf(Tenders, tVal5)
.Offset(0, 1).Select
End With
With ActiveCell
.Value = Application.WorksheetFunction.CountIf(Tenders, tVal6)
.Offset(0, 1).Select
End With
With ActiveCell
.Value = Application.WorksheetFunction.CountIf(Tenders, tVal7)
.Offset(0, 1).Select
End With
With ActiveCell
.Value = Application.WorksheetFunction.CountIf(Tenders, tVal8)
.Offset(0, 1).Select
End With
With ActiveCell
.Value = Application.WorksheetFunction.CountIf(Tenders, tVal9)
.Offset(0, 1).Select
End With
With ActiveCell
.Value = Application.WorksheetFunction.CountIf(Tenders, tVal10)
.Offset(0, 1).Select
End With
With ActiveCell
.Value = Application.WorksheetFunction.CountIf(Tenders, tVal11)
.Offset(0, 1).Select
End With
With ActiveCell
.Value = Application.WorksheetFunction.CountIf(Tenders, tVal12)
.Offset(0, 1).Select
End With
With ActiveCell
.Value = Application.WorksheetFunction.CountIf(Tenders, tVal13)
.Offset(0, 1).Select
End With
With ActiveCell
.Value = Application.WorksheetFunction.CountIf(Tenders, tVal14)
.Offset(0, 1).Select
End With
With ActiveCell
.Value = Application.WorksheetFunction.CountIf(Tenders, tVal15)
.Offset(0, 1).Select
End With
With ActiveCell
.Value = Application.WorksheetFunction.CountIf(Tenders, tVal16)
.Offset(0, 1).Select
End With
With ActiveCell
.Value = Application.WorksheetFunction.CountIf(Tenders, tVal17)
.Offset(0, 1).Select
End With
With ActiveCell
.Value = Application.WorksheetFunction.CountIf(Tenders, tVal18)
.Offset(0, 1).Select
End With
With ActiveCell
.Value = Application.WorksheetFunction.CountIf(Tenders, tVal19)
.Offset(0, 1).Select
End With
With ActiveCell
.Value = Application.WorksheetFunction.CountIf(Tenders, tVal20)
.Offset(0, 1).Select
End With
With ActiveCell
.Value = Application.WorksheetFunction.CountIf(Tenders, tVal21)
.Offset(0, 1).Select
End With
With ActiveCell
.Value = Application.WorksheetFunction.CountIf(Tenders, tVal22)
.Offset(0, 1).Select
End With
With ActiveCell
.Value = Application.WorksheetFunction.CountIf(Tenders, tVal23)
.Offset(0, 1).Select
End With
With ActiveCell
.Value = Application.WorksheetFunction.CountIf(Tenders, tVal24)
End With
End Sub
TIA
Mick