Skip to content

Instantly share code, notes, and snippets.

@jrowda
Created August 4, 2016 16:12
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
Star You must be signed in to star a gist
Save jrowda/6aad120cdc6b4673ee5a2a590c5872a9 to your computer and use it in GitHub Desktop.
Sub getSCORE()
'
' subtotal Macro
Columns("A:A").Select
Selection.subtotal GroupBy:=1, Function:=xlCount, TotalList:=Array(1), _
Replace:=True, PageBreaks:=False, SummaryBelowData:=True
' addformulas Macro
Range("F4").Select
ActiveCell.FormulaR1C1 = _
"=IF(LEN(TRIM(R[-2]C[-2]))=0,0,LEN(TRIM(R[-2]C[-2]))-LEN(SUBSTITUTE(R[-2]C[-2],"" "",""""))+1)"
Range("F4").Select
ActiveCell.FormulaR1C1 = _
"=IF(LEN(TRIM(RC[-2]))=0,0,LEN(TRIM(RC[-2]))-LEN(SUBSTITUTE(RC[-2],"" "",""""))+1)"
Range("F4").Select
Selection.AutoFill Destination:=Range("F4:F3534")
Range("F4:F3534").Select
Range("G4").Select
ActiveCell.FormulaR1C1 = _
"=IF(ISBLANK(RC[-3]),R[-1]C[-1],IF(LEN(TRIM(RC[-3]))=0,0,LEN(TRIM(RC[-3]))-LEN(SUBSTITUTE(RC[-3],"" "",""""))+1))"
Range("G4").Select
Selection.AutoFill Destination:=Range("G4:G3534")
Range("G4:G3534").Select
' addscore Macro
Range("H4").Select
ActiveCell.FormulaR1C1 = "=IF(ISBLANK(RC[-7]),"""",RC[-6]/RC[-1])"
Range("H4").Select
Selection.AutoFill Destination:=Range("H4:H3534")
Range("H4:H3534").Select
' decimals Macro
Columns("H:H").Select
Selection.NumberFormat = "0.000"
' remove nonbreaking spaces
Columns("D:D").Select
Selection.Replace What:=" ", Replacement:=" ", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
' format Macro
Range("A1").Select
ActiveCell.FormulaR1C1 = "SEGMENT ID"
Range("B1").Select
ActiveCell.FormulaR1C1 = "COUNT"
Range("C1").Select
ActiveCell.FormulaR1C1 = "Error"
Range("D1").Select
ActiveCell.FormulaR1C1 = "Source"
Range("E1").Select
ActiveCell.FormulaR1C1 = "Target"
Columns("F:F").Select
Selection.EntireColumn.Hidden = True
Range("G1").Select
ActiveCell.FormulaR1C1 = "Words"
Range("H1").Select
ActiveCell.FormulaR1C1 = "Score"
Range("A2").Select
ActiveCell.FormulaR1C1 = "Filename:"
Columns("A:A").Select
Selection.AutoFilter
ActiveSheet.Range("$A$1:$A$3535").AutoFilter Field:=1, Criteria1:="<>"
ActiveSheet.Range("$A$1:$A$3535").AutoFilter Field:=1
Columns("A:H").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
'ActiveSheet.Range("$A$1:$A$3535").AutoFilter Field:=1, Criteria1:="<>"
'ActiveSheet.Range("$A$1:$A$3535").AutoFilter Field:=1
Range("A1:H1").Select
Selection.Font.Bold = True
' color Macro
Columns("H:H").Select
Selection.FormatConditions.AddTop10
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1)
.TopBottom = xlTop10Bottom
.Rank = 10
.Percent = True
End With
With Selection.FormatConditions(1).Font
.Color = -16752384
.TintAndShade = 0
End With
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.Color = 13561798
.TintAndShade = 0
End With
Selection.FormatConditions(1).StopIfTrue = False
Selection.FormatConditions.AddTop10
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1)
.TopBottom = xlTop10Top
.Rank = 10
.Percent = True
End With
With Selection.FormatConditions(1).Font
.Color = -16383844
.TintAndShade = 0
End With
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.Color = 13551615
.TintAndShade = 0
End With
Selection.FormatConditions(1).StopIfTrue = False
End Sub
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment