Hi folks,
I didn't think it was worth just dropping in a few bits of the code as it might be clearer with the whole lot.Basically i am quite slow when it comes to what exactly is being said in programming code.
The result when running a command button is i get a #NAME? error.I have highlighted where abouts the code where it seems to be going wrong. This is not my code and hence not sure what is going on with it.Technically the code should probably be correct (as it has run before) but when i have opened the s/s where the below code lives in and the s/s it refers to Employee.csv and name the range "emea" in that s/s, run the command button it runs perfectly but for this odd supplementary column code where it is adding "Other" rather than code Department Names it is meant to be reading from the Employee.csv file. I know this is an airy thread but i thought someone might spot something stupid which i am or am not doing. Cheers
Sub ArrangeData()
'
' ArrangeData Macro
' Macro recorded 27/06/2005 by Manoj
'
findSheet("User Stats").Activate
Columns("C:C").Select
Selection.Delete Shift:=xlToLeft
Columns("D
").Select
Selection.Insert Shift:=xlToRight
Rows("1:1").Select
Selection.Insert Shift:=xlDown
Range("A1").Select
ActiveCell.FormulaR1C1 = "User Name"
Range("B1").Select
ActiveCell.FormulaR1C1 = "SID"
Range("C1").Select
ActiveCell.FormulaR1C1 = "Documentum Group"
Range("D1").Select
ActiveCell.FormulaR1C1 = "Top Level Group"
Range("E1").Select
ActiveCell.FormulaR1C1 = "Usage This Week"
Range("F1").Select
ActiveCell.FormulaR1C1 = "Usage Till Date"
Cells.Select
Cells.EntireRow.AutoFit
Range("F2").Select
Columns("A:A").ColumnWidth = 8.57
Columns("A:A").EntireColumn.AutoFit
Columns("B:B").EntireColumn.AutoFit
Columns("C:C").EntireColumn.AutoFit
Columns("D").EntireColumn.AutoFit
Columns("E:E").EntireColumn.AutoFit
Columns("F:F").EntireColumn.AutoFit
Columns("A:A").ColumnWidth = 13.43
Cells.Select
Cells.EntireRow.AutoFit
Range("A1:F1").Select
Selection.Font.Bold = True
Range("A1:F1").Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlInsideVertical)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlInsideHorizontal)
End With
Range("A1:F1").Select
Selection.AutoFilter
Range("D21000").Select
ActiveWorkbook.Names.Add Name:="groups", RefersToR1C1:= _
"='User stats'!R2C4:R1000C4"
Range("C21000").Select
ActiveWorkbook.Names.Add Name:="subgroups", RefersToR1C1:= _
"='User stats'!R2C3:R1000C3"
End Sub
Public Sub TopLevelSort()
Dim currentSheet
Set currentSheet = findSheet("User Stats")
Dim index As Integer
Dim currentValue As String
index = 2
Do While index < 1000
'check the value
currentValue = currentSheet.Cells(index, 3)
If currentValue = "dcm" Then
currentSheet.Cells(index, 4) = "DCM"
ElseIf currentValue = "loan trading" Then
currentSheet.Cells(index, 4) = "DCM"
ElseIf currentValue = "dcm - leverage finance" Then
currentSheet.Cells(index, 4) = "DCM"
ElseIf currentValue = "corporate banking - corporates" Then
currentSheet.Cells(index, 4) = "GCRM"
ElseIf currentValue = "corporate banking - fig" Then
currentSheet.Cells(index, 4) = "GCRM"
ElseIf currentValue = "account admin" Then
currentSheet.Cells(index, 4) = "IT Support"
ElseIf currentValue = "ad build" Then
currentSheet.Cells(index, 4) = "IT Support"
ElseIf currentValue = "ad operate" Then
currentSheet.Cells(index, 4) = "IT Support"
ElseIf currentValue = "business power users" Then
currentSheet.Cells(index, 4) = "Business Power Users"
ElseIf currentValue = "corporate banking - fs" Then
currentSheet.Cells(index, 4) = "GCRM"
ElseIf currentValue = "credit executives" Then
currentSheet.Cells(index, 4) = "GCRM"
ElseIf currentValue = "dcm mo" Then
currentSheet.Cells(index, 4) = "DCM MO"
ElseIf currentValue = "desktop support" Then
currentSheet.Cells(index, 4) = "IT Support"
ElseIf currentValue = "desktop support new" Then
currentSheet.Cells(index, 4) = "IT Support"
ElseIf currentValue = "doc control group" Then
currentSheet.Cells(index, 4) = "Doc Control Group"
ElseIf currentValue = "gcrm" Then
currentSheet.Cells(index, 4) = "gcrm"
ElseIf currentValue = "gcrmo corps/fig support" Then
currentSheet.Cells(index, 4) = "GCRM MO"
ElseIf currentValue = "gcrmo funds support" Then
currentSheet.Cells(index, 4) = "GCRM MO"
ElseIf currentValue = "gcrmo projects" Then
currentSheet.Cells(index, 4) = "GCRM MO"
ElseIf currentValue = "gcrmo reporting" Then
currentSheet.Cells(index, 4) = "GCRM MO"
ElseIf currentValue = "gcrmo special credit" Then
currentSheet.Cells(index, 4) = "GCRM MO"
ElseIf currentValue = "loans agency" Then
currentSheet.Cells(index, 4) = "GCRM MO"
ElseIf currentValue = "ib credit review" Then
currentSheet.Cells(index, 4) = "GCRM"
ElseIf currentValue = "no group" Then
currentSheet.Cells(index, 4) = "No Group"
ElseIf currentValue = "presentations" Then
currentSheet.Cells(index, 4) = "Presentations"
ElseIf currentValue = "special credit" Then
currentSheet.Cells(index, 4) = "GCRM"
ElseIf currentValue = "team usa" Then
currentSheet.Cells(index, 4) = "GCRM MO"
ElseIf currentValue = "teg" Then
currentSheet.Cells(index, 4) = "TEG"
ElseIf currentValue = "teg admin" Then
currentSheet.Cells(index, 4) = "TEG Admin"
ElseIf currentValue = "bond p&l mo" Then
currentSheet.Cells(index, 4) = "DCM MO"
Else
End If
index = index + 1
Loop
End Sub
Sub GCRMUsageSheet()
'
' GCRMUsageSheet Macro
' Macro recorded 27/06/2005 by Manoj
'
'
findSheet("GCRM Usage").Activate
Range("B2").Select
ActiveCell.FormulaR1C1 = "=COUNTIF(groups,""=GCRM"")"
Range("B3").Select
ActiveCell.FormulaR1C1 = _
"=COUNTIF(subgroups,""=corporate banking - corporates"")"
Range("B4").Select
ActiveCell.FormulaR1C1 = "=COUNTIF(subgroups,""=corporate banking - fig"")"
Range("B5").Select
ActiveCell.FormulaR1C1 = "=COUNTIF(subgroups,""=corporate banking - fs"")"
Range("B6").Select
ActiveCell.FormulaR1C1 = "=COUNTIF(subgroups,""=credit executives"")"
Range("B7").Select
ActiveCell.FormulaR1C1 = "=COUNTIF(subgroups,""=special credit"")"
Range("B8").Select
ActiveCell.FormulaR1C1 = "=COUNTIF(subgroups,""=ib credit review"")"
Range("B9").Select
ActiveCell.FormulaR1C1 = "=COUNTIF(subgroups,""=gcrm"")"
Range("B11").Select
ActiveCell.FormulaR1C1 = "=COUNTIF(groups,""=GCRM MO"")"
Range("B12").Select
ActiveCell.FormulaR1C1 = "=COUNTIF(subgroups,""=gcrmo corps/fig support"")"
Range("B13").Select
ActiveCell.FormulaR1C1 = "=COUNTIF(subgroups,""=gcrmo funds support"")"
Range("B14").Select
ActiveCell.FormulaR1C1 = "=COUNTIF(subgroups,""=gcrmo reporting"")"
Range("B15").Select
ActiveCell.FormulaR1C1 = "=COUNTIF(subgroups,""=gcrmo projects"")"
Range("B16").Select
ActiveCell.FormulaR1C1 = "=COUNTIF(subgroups,""=team usa"")"
Range("B17").Select
ActiveCell.FormulaR1C1 = "=COUNTIF(subgroups,""=loans agency"")"
Range("C2").Select
Selection.FormulaArray = _
"=SUM(IF(groups=""gcrm"",IF('User Stats'!RC5:R[998]C5>0,1,0)))"
Range("C3").Select
Selection.FormulaArray = _
"=SUM(IF(subgroups=""corporate banking - corporates"",IF('User Stats'!R[-1]C5:R[997]C5>0,1,0)))"
Range("C4").Select
Selection.FormulaArray = _
"=SUM(IF(subgroups=""corporate banking - fig"",IF('User Stats'!R[-2]C5:R[996]C5>0,1,0)))"
Range("C5").Select
Selection.FormulaArray = _
"=SUM(IF(subgroups=""corporate banking - fs"",IF('User Stats'!R[-3]C5:R[995]C5>0,1,0)))"
Range("C6").Select
Selection.FormulaArray = _
"=SUM(IF(subgroups=""credit executives"",IF('User Stats'!R[-4]C5:R[994]C5>0,1,0)))"
Range("C7").Select
Selection.FormulaArray = _
"=SUM(IF(subgroups=""special credit"",IF('User Stats'!R[-5]C5:R[993]C5>0,1,0)))"
Range("C8").Select
Selection.FormulaArray = _
"=SUM(IF(subgroups=""ib credit review"",IF('User Stats'!R[-6]C5:R[992]C5>0,1,0)))"
Range("C9").Select
Selection.FormulaArray = _
"=SUM(IF(subgroups=""gcrm"",IF('User Stats'!R[-7]C5:R[991]C5>0,1,0)))"
Range("C11").Select
Selection.FormulaArray = _
"=SUM(IF(groups=""gcrm mo"",IF('User Stats'!R[-9]C5:R[989]C5>0,1,0)))"
Range("C12").Select
Selection.FormulaArray = _
"=SUM(IF(subgroups=""gcrmo corps/fig support"",IF('User Stats'!R[-10]C5:R[988]C5>0,1,0)))"
Range("C13").Select
Selection.FormulaArray = _
"=SUM(IF(subgroups=""gcrmo funds support"",IF('User Stats'!R[-11]C5:R[987]C5>0,1,0)))"
Range("C14").Select
Selection.FormulaArray = _
"=SUM(IF(subgroups=""gcrmo reporting"",IF('User Stats'!R[-12]C5:R[986]C5>0,1,0)))"
Range("C15").Select
Selection.FormulaArray = _
"=SUM(IF(subgroups=""gcrmo projects"",IF('User Stats'!R[-13]C5:R[985]C5>0,1,0)))"
Range("C16").Select
Selection.FormulaArray = _
"=SUM(IF(subgroups=""team usa"",IF('User Stats'!R[-14]C5:R[984]C5>0,1,0)))"
Range("C17").Select
Selection.FormulaArray = _
"=SUM(IF(subgroups=""loans agency"",IF('User Stats'!R[-15]C5:R[983]C5>0,1,0)))"
Range("D2").Select
ActiveCell.FormulaR1C1 = "=RC[-1]/RC[-2]"
Range("D3").Select
ActiveCell.FormulaR1C1 = "=RC[-1]/RC[-2]"
Range("D4").Select
ActiveCell.FormulaR1C1 = "=RC[-1]/RC[-2]"
Range("D5").Select
ActiveCell.FormulaR1C1 = "=RC[-1]/RC[-2]"
Range("D6").Select
ActiveCell.FormulaR1C1 = "=RC[-1]/RC[-2]"
Range("D7").Select
ActiveCell.FormulaR1C1 = "=RC[-1]/RC[-2]"
Range("D8").Select
ActiveCell.FormulaR1C1 = "=RC[-1]/RC[-2]"
Range("D9").Select
ActiveCell.FormulaR1C1 = "=RC[-1]/RC[-2]"
Range("D11").Select
ActiveCell.FormulaR1C1 = "=RC[-1]/RC[-2]"
Range("D12").Select
ActiveCell.FormulaR1C1 = "=RC[-1]/RC[-2]"
Range("D13").Select
ActiveCell.FormulaR1C1 = "=RC[-1]/RC[-2]"
Range("D14").Select
ActiveCell.FormulaR1C1 = "=RC[-1]/RC[-2]"
Range("D15").Select
ActiveCell.FormulaR1C1 = "=RC[-1]/RC[-2]"
Range("D16").Select
ActiveCell.FormulaR1C1 = "=RC[-1]/RC[-2]"
Range("D16").Select
Selection.Style = "Percent"
Range("D17").Select
ActiveCell.FormulaR1C1 = "=RC[-1]/RC[-2]"
Range("D17").Select
Selection.Style = "Percent"
Range("E2").Select
Selection.FormulaArray = _
"=SUM(IF(groups=""gcrm"",IF('User Stats'!RC6:R[998]C6>0,1,0)))"
Range("E3").Select
Selection.FormulaArray = _
"=SUM(IF(subgroups=""corporate banking - corporates"",IF('User Stats'!R[-1]C6:R[997]C6>0,1,0)))"
Range("E4").Select
Selection.FormulaArray = _
"=SUM(IF(subgroups=""corporate banking - fig"",IF('User Stats'!R[-2]C6:R[996]C6>0,1,0)))"
Range("E5").Select
Selection.FormulaArray = _
"=SUM(IF(subgroups=""corporate banking - fs"",IF('User Stats'!R[-3]C6:R[995]C6>0,1,0)))"
Range("E6").Select
Selection.FormulaArray = _
"=SUM(IF(subgroups=""credit executives"",IF('User Stats'!R[-4]C6:R[994]C6>0,1,0)))"
Range("E7").Select
Selection.FormulaArray = _
"=SUM(IF(subgroups=""special credit"",IF('User Stats'!R[-5]C6:R[993]C6>0,1,0)))"
Range("E8").Select
Selection.FormulaArray = _
"=SUM(IF(subgroups=""ib credit review"",IF('User Stats'!R[-6]C6:R[992]C6>0,1,0)))"
Range("E9").Select
Selection.FormulaArray = _
"=SUM(IF(subgroups=""gcrm"",IF('User Stats'!R[-7]C6:R[991]C6>0,1,0)))"
Range("E11").Select
Selection.FormulaArray = _
"=SUM(IF(groups=""gcrm mo"",IF('User Stats'!R[-9]C6:R[989]C6>0,1,0)))"
Range("E12").Select
Selection.FormulaArray = _
"=SUM(IF(subgroups=""gcrmo corps/fig support"",IF('User Stats'!R[-10]C6:R[988]C6>0,1,0)))"
Range("E13").Select
Selection.FormulaArray = _
"=SUM(IF(subgroups=""gcrmo funds support"",IF('User Stats'!R[-11]C6:R[987]C6>0,1,0)))"
Range("E14").Select
Selection.FormulaArray = _
"=SUM(IF(subgroups=""gcrmo reporting"",IF('User Stats'!R[-12]C6:R[986]C6>0,1,0)))"
Range("E15").Select
Selection.FormulaArray = _
"=SUM(IF(subgroups=""gcrmo projects"",IF('User Stats'!R[-13]C6:R[985]C6>0,1,0)))"
Range("E16").Select
Selection.FormulaArray = _
"=SUM(IF(subgroups=""team usa"",IF('User Stats'!R[-14]C6:R[984]C6>0,1,0)))"
Range("E17").Select
Selection.FormulaArray = _
"=SUM(IF(subgroups=""loans agency"",IF('User Stats'!R[-15]C6:R[983]C6>0,1,0)))"
Range("F2").Select
ActiveCell.FormulaR1C1 = "=RC[-1]/RC[-4]"
Range("F3").Select
ActiveCell.FormulaR1C1 = "=RC[-1]/RC[-4]"
Range("F4").Select
ActiveCell.FormulaR1C1 = "=RC[-1]/RC[-4]"
Range("F5").Select
ActiveCell.FormulaR1C1 = "=RC[-1]/RC[-4]"
Range("F6").Select
ActiveCell.FormulaR1C1 = "=RC[-1]/RC[-4]"
Range("F7").Select
ActiveCell.FormulaR1C1 = "=RC[-1]/RC[-4]"
Range("F8").Select
ActiveCell.FormulaR1C1 = "=RC[-1]/RC[-4]"
Range("F9").Select
ActiveCell.FormulaR1C1 = "=RC[-1]/RC[-4]"
Range("F11").Select
ActiveCell.FormulaR1C1 = "=RC[-1]/RC[-4]"
Range("F12").Select
ActiveCell.FormulaR1C1 = "=RC[-1]/RC[-4]"
Range("F13").Select
ActiveCell.FormulaR1C1 = "=RC[-1]/RC[-4]"
Range("F14").Select
ActiveCell.FormulaR1C1 = "=RC[-1]/RC[-4]"
Range("F15").Select
ActiveCell.FormulaR1C1 = "=RC[-1]/RC[-4]"
Range("F16").Select
ActiveCell.FormulaR1C1 = "=RC[-1]/RC[-4]"
Range("F16").Select
Selection.Style = "Percent"
Range("F17").Select
ActiveCell.FormulaR1C1 = "=RC[-1]/RC[-4]"
Range("F17").Select
Selection.Style = "Percent"
Range("D2").Select
ActiveCell.FormulaR1C1 = "=RC[-1]/RC[-2]"
Range("D3").Select
ActiveCell.FormulaR1C1 = "=RC[-1]/RC[-2]"
Range("D4").Select
ActiveCell.FormulaR1C1 = "=RC[-1]/RC[-2]"
Range("D5").Select
ActiveCell.FormulaR1C1 = "=RC[-1]/RC[-2]"
Range("D6").Select
ActiveCell.FormulaR1C1 = "=RC[-1]/RC[-2]"
Range("D7").Select
End Sub
Sub UsageSummary()
'
' UsageSummary Macro
' Macro recorded 27/06/2005 by Manoj
'
'
findSheet("Reporting").Activate
Range("C5").Select
ActiveCell.FormulaR1C1 = "=COUNTIF(groups,""=dcm"")"
Range("C6").Select
Selection.FormulaArray = _
"=SUM(IF(groups=""dcm"",IF('User Stats'!R[-4]C5:R[994]C5>0,1,0)))"
Range("C8").Select
ActiveCell.FormulaR1C1 = "=R[-2]C/R[-3]C"
Range("D5").Select
ActiveCell.FormulaR1C1 = "=COUNTIF(groups,""=GCRM"")"
Range("D6").Select
ActiveCell.FormulaR1C1 = _
"=SUM(IF(groups=""gcrm"",IF('User Stats'!R[-4]C5:R[994]C5>0,1,0)))"
Range("D6").Select
Selection.FormulaArray = _
"=SUM(IF(groups=""gcrm"",IF('User Stats'!R[-4]C5:R[994]C5>0,1,0)))"
Range("D8").Select
ActiveCell.FormulaR1C1 = "=R[-2]C/R[-3]C"
Range("E5").Select
ActiveCell.FormulaR1C1 = "=COUNTIF(groups,""=gcrm mo"")"
Range("E6").Select
Selection.FormulaArray = _
"=SUM(IF(groups=""gcrm mo"",IF('User Stats'!R[-4]C5:R[994]C5>0,1,0)))"
Range("E8").Select
ActiveCell.FormulaR1C1 = "=R[-2]C/R[-3]C"
Range("F5").Select
ActiveCell.FormulaR1C1 = "=COUNTIF(groups,""=dcm mo"")"
Range("F6").Select
Selection.FormulaArray = _
"=SUM(IF(groups=""dcm mo"",IF('User Stats'!R[-4]C5:R[994]C5>0,1,0)))"
Range("F8").Select
ActiveCell.FormulaR1C1 = "=R[-2]C/R[-3]C"
Range("G5").Select
ActiveCell.FormulaR1C1 = "=COUNTIF(groups,""=doc control group"")"
Range("G6").Select
Selection.FormulaArray = _
"=SUM(IF(groups=""doc control group"",IF('User Stats'!R[-4]C5:R[994]C5>0,1,0)))"
Range("G8").Select
ActiveCell.FormulaR1C1 = "=R[-2]C/R[-3]C"
Range("G12").Select
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 3
Range("H5").Select
ActiveCell.FormulaR1C1 = "=(COUNTIF(groups,""=TEG"")+COUNTIF(groups,""=Teg Admin""))"
Range("H6").Select
Selection.FormulaArray = _
"=(SUM(IF(groups=""teg"",IF('User Stats'!R[-4]C5:R[994]C5>0,1,0)))+SUM(IF(groups=""teg admin"",IF('User Stats'!R[-4]C5:R[994]C5>0,1,0))))"
Range("H8").Select
ActiveCell.FormulaR1C1 = "=R[-2]C/R[-3]C"
Range("I5").Select
ActiveCell.FormulaR1C1 = "=COUNTIF(groups,""=presentations"")"
Range("I6").Select
Selection.FormulaArray = _
"=SUM(IF(groups=""Presentations"",IF('User Stats'!R[-4]C5:R[994]C5>0,1,0)))"
Range("I8").Select
ActiveCell.FormulaR1C1 = "=R[-2]C/R[-3]C"
Range("J5").Select
ActiveCell.FormulaR1C1 = _
"=COUNTIF(groups,""=IT support"")+COUNTIF(groups,""=AD operate"")+COUNTIF(groups,""=AD build"")"
Range("J6").Select
Selection.FormulaArray = _
"=SUM(IF(groups=""IT Support"",IF('User Stats'!R[-4]C5:R[994]C5>0,1,0)))"
Range("J8").Select
ActiveCell.FormulaR1C1 = "=R[-2]C/R[-3]C"
Range("K5").Select
ActiveCell.FormulaR1C1 = "=COUNTIF(groups,""=business power users"")"
Range("K6").Select
Selection.FormulaArray = _
"=SUM(IF(groups=""business power users"",IF('User Stats'!R[-4]C5:R[994]C5>0,1,0)))"
Range("J6").Select
Selection.FormulaArray = _
"=SUM(IF(groups=""IT Support"",IF('User Stats'!R[-4]C5:R[994]C5>0,1,0)))"
Range("K8").Select
ActiveCell.FormulaR1C1 = "=R[-2]C/R[-3]C"
Range("L5").Select
ActiveCell.FormulaR1C1 = "=COUNTIF(groups,""=no group"")"
Range("L6").Select
Selection.FormulaArray = _
"=SUM(IF(groups=""no group"",IF('User Stats'!R[-4]C5:R[994]C5>0,1,0)))"
Range("L8").Select
ActiveCell.FormulaR1C1 = "=R[-2]C/R[-3]C"
Range("M5").Select
ActiveCell.FormulaR1C1 = "=SUM(RC[-10]:RC[-1])"
Range("M6").Select
ActiveCell.FormulaR1C1 = "=SUM(RC[-10]:RC[-1])"
Range("M8").Select
ActiveCell.FormulaR1C1 = "=R[-2]C/R[-3]C"
Range("M5:M6").Select
ActiveWindow.LargeScroll ToRight:=-1
Range("C5:M8").Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlInsideVertical)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlInsideHorizontal)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlInsideVertical)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlInsideHorizontal)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
Range("G14").Select
End Sub
Public Sub Omnibus()
Call Module1.ArrangeData
Call Module1.TopLevelSort
Call Module1.UsageSummary
Call Module1.GCRMUsageSheet
Call Module1.FindDCMSubDept
Call Module1.OtherCalc
Call Module1.DCMUsageSheet
End Sub
'Finds a worksheet based on the sheetName (WARNING CASE-SENSITIVE!), returns NOTHING if not found
'i.e. use IsEmpty() to check
Public Function findSheet(sheetName As String) As Worksheet
Dim index As Integer
Dim currentSheet As Worksheet
index = 1
Do While (index <= ActiveWorkbook.Sheets.Count)
Set currentSheet = ActiveWorkbook.Sheets(index)
If (currentSheet.Name = sheetName) Then
Set findSheet = currentSheet
Exit Function
End If
index = index + 1
Loop
End Function
Public Sub findSheetTest()
If (IsEmpty(findSheet("manojlikeshisdancing"))) Then
MsgBox ("EMPTY TEST DISGRACE!")
End
ElseIf (findSheet("User Stats").Name <> "User Stats") Then
MsgBox ("SHOCK SEARCH SCANDAL, Failure on name test [User Stats]")
End
Else
MsgBox "FINDSHEET BID SUCCESS!"
End If
End Sub
******************************************************
I THINK THIS IS THE PROCEDURE WHICH IS DUMPING THE INCORRECT VALUES INTO THE "G" COLUMN
Public Sub OtherCalc()
Dim currentSheet
Set currentSheet = findSheet("User Stats")
Dim index As Integer
findSheet("User Stats").Activate
'd.Add "Conduits", "4"
'd.Add "key", "1"
Dim d, i 'Create some variables
Set d = CreateObject("Scripting.Dictionary")
d.Add "PFG CENTRAL EXP", "1" 'Add some keys and items.
d.Add "SECONDARY LOAN TRADING 4", "1"
d.Add "CONDUITS 4", "1"
d.Add "UK HIGH YIELD CORP FINANCE 4", "1"
d.Add "FS 4", "1"
d.Add "FIGRE 44289", "6"
d.Add "GENERAL INDUSTRIES 4", "1"
d.Add "DCM CO LOAN CAPITAL MARKETS 44", "1"
d.Add "DCM CO LOAN DISTR DESK 4", "1"
d.Add "LONDON CAPITAL MARKETS 44289", "1"
d.Add "RATINGS ADVISORY 44289", "1"
d.Add "EMEA DCM MGMT-H-CHASE 4", "1"
d.Add "JPSPRD EUR-BNK063723516UK 4428", "1"
d.Add "JPRESF 063726578UK 4", "1"
d.Add "JPEM EU CAP MK063703207UK 4428", "1"
d.Add "JPEURP CREDT A063726676UK 4428", "1"
d.Add "CAZ JV DMC", "18"
index = 1
Do While index < 1000
If (currentSheet.Cells(index, 4)) = "DCM" Then
If Not (d.Exists(currentSheet.Cells(index, 7).Value)) Then
currentSheet.Cells(index, 7) = "Other"
End If
End If
index = index + 1
Loop
End Sub
*******************************************************
Sub FindDCMSubDept()
'
' FindDCMSubDept Macro
' Macro recorded 20/07/2005 by Manoj Manoharan
'
'
findSheet("User Stats").Activate
Range("G1").Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
Selection.Borders(xlEdgeTop).LineStyle = xlNone
Selection.Borders(xlEdgeBottom).LineStyle = xlNone
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
Selection.Borders(xlInsideVertical).LineStyle = xlNone
Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
ActiveCell.FormulaR1C1 = "E-Source Department Name"
With ActiveCell.Characters(Start:=1, Length:=24).Font
.Name = "Arial"
.FontStyle = "Bold"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
End With
Range("A1:G1").Select
Range("G1").Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
Range("G2").Select
Application.Goto Reference:="depnames"
ActiveWindow.ScrollRow = 3
ActiveWindow.ScrollRow = 1
ActiveWorkbook.Names("depnames").Delete
ActiveWorkbook.Names("usernames").Delete
Range("G2").Select
ActiveWorkbook.Names.Add Name:="depnames", RefersToR1C1:= _
"='User Stats'!R2C7:R1000C7"
ActiveWorkbook.Names.Add Name:="usernames", RefersToR1C1:= _
"='User Stats'!R2C1:R1000C1"
ActiveWorkbook.Names.Add Name:="usernames", RefersToR1C1:= _
"='User Stats'!R2C1:R1000C1"
Application.Goto Reference:="usernames"
ActiveWindow.ScrollRow = 1
ActiveWindow.ScrollRow = 3
ActiveWindow.ScrollRow = 5
ActiveWindow.ScrollRow = 10
ActiveWindow.ScrollRow = 14
ActiveWindow.ScrollRow = 18
ActiveWindow.ScrollRow = 23
ActiveWindow.ScrollRow = 31
ActiveWindow.ScrollRow = 40
ActiveWindow.ScrollRow = 48
ActiveWindow.ScrollRow = 55
ActiveWindow.ScrollRow = 64
ActiveWindow.ScrollRow = 72
ActiveWindow.ScrollRow = 83
ActiveWindow.ScrollRow = 89
ActiveWindow.ScrollRow = 100
ActiveWindow.ScrollRow = 107
ActiveWindow.ScrollRow = 118
ActiveWindow.ScrollRow = 124
ActiveWindow.ScrollRow = 128
ActiveWindow.ScrollRow = 130
ActiveWindow.ScrollRow = 131
ActiveWindow.ScrollRow = 130
ActiveWindow.ScrollRow = 128
ActiveWindow.ScrollRow = 124
ActiveWindow.ScrollRow = 116
ActiveWindow.ScrollRow = 107
ActiveWindow.ScrollRow = 94
ActiveWindow.ScrollRow = 83
ActiveWindow.ScrollRow = 74
ActiveWindow.ScrollRow = 62
ActiveWindow.ScrollRow = 51
ActiveWindow.ScrollRow = 38
ActiveWindow.ScrollRow = 29
ActiveWindow.ScrollRow = 16
ActiveWindow.ScrollRow = 5
ActiveWindow.ScrollRow = 1
Range("G2").Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(User Name,Employee.csv!emea,31,FALSE)"
Range("G2").Select
Selection.AutoFill Destination:=Range("G2:G1000"), Type:=xlFillDefault
Range("G2:G1000").Select
ActiveWindow.ScrollRow = 981
ActiveWindow.ScrollRow = 979
ActiveWindow.ScrollRow = 977
ActiveWindow.ScrollRow = 975
ActiveWindow.ScrollRow = 974
ActiveWindow.ScrollRow = 972
ActiveWindow.ScrollRow = 968
ActiveWindow.ScrollRow = 964
ActiveWindow.ScrollRow = 960
ActiveWindow.ScrollRow = 957
ActiveWindow.ScrollRow = 951
ActiveWindow.ScrollRow = 940
ActiveWindow.ScrollRow = 932
ActiveWindow.ScrollRow = 921
ActiveWindow.ScrollRow = 910
ActiveWindow.ScrollRow = 897
ActiveWindow.ScrollRow = 885
ActiveWindow.ScrollRow = 872
ActiveWindow.ScrollRow = 850
ActiveWindow.ScrollRow = 829
ActiveWindow.ScrollRow = 810
ActiveWindow.ScrollRow = 797
ActiveWindow.ScrollRow = 782
ActiveWindow.ScrollRow = 761
ActiveWindow.ScrollRow = 746
ActiveWindow.ScrollRow = 735
ActiveWindow.ScrollRow = 716
ActiveWindow.ScrollRow = 701
ActiveWindow.ScrollRow = 688
ActiveWindow.ScrollRow = 677
ActiveWindow.ScrollRow = 669
ActiveWindow.ScrollRow = 658
ActiveWindow.ScrollRow = 651
ActiveWindow.ScrollRow = 643
ActiveWindow.ScrollRow = 632
ActiveWindow.ScrollRow = 624
ActiveWindow.ScrollRow = 615
ActiveWindow.ScrollRow = 607
ActiveWindow.ScrollRow = 596
ActiveWindow.ScrollRow = 583
ActiveWindow.ScrollRow = 570
ActiveWindow.ScrollRow = 561
ActiveWindow.ScrollRow = 544
ActiveWindow.ScrollRow = 534
ActiveWindow.ScrollRow = 521
ActiveWindow.ScrollRow = 502
ActiveWindow.ScrollRow = 489
ActiveWindow.ScrollRow = 476
ActiveWindow.ScrollRow = 461
ActiveWindow.ScrollRow = 450
ActiveWindow.ScrollRow = 440
ActiveWindow.ScrollRow = 427
ActiveWindow.ScrollRow = 414
ActiveWindow.ScrollRow = 405
ActiveWindow.ScrollRow = 397
ActiveWindow.ScrollRow = 392
ActiveWindow.ScrollRow = 382
ActiveWindow.ScrollRow = 377
ActiveWindow.ScrollRow = 369
ActiveWindow.ScrollRow = 363
ActiveWindow.ScrollRow = 356
ActiveWindow.ScrollRow = 350
ActiveWindow.ScrollRow = 343
ActiveWindow.ScrollRow = 335
ActiveWindow.ScrollRow = 324
ActiveWindow.ScrollRow = 316
ActiveWindow.ScrollRow = 307
ActiveWindow.ScrollRow = 301
ActiveWindow.ScrollRow = 296
ActiveWindow.ScrollRow = 292
ActiveWindow.ScrollRow = 286
ActiveWindow.ScrollRow = 281
ActiveWindow.ScrollRow = 275
ActiveWindow.ScrollRow = 270
ActiveWindow.ScrollRow = 262
ActiveWindow.ScrollRow = 253
ActiveWindow.ScrollRow = 245
ActiveWindow.ScrollRow = 239
ActiveWindow.ScrollRow = 234
ActiveWindow.ScrollRow = 230
ActiveWindow.ScrollRow = 221
ActiveWindow.ScrollRow = 215
ActiveWindow.ScrollRow = 208
ActiveWindow.ScrollRow = 200
ActiveWindow.ScrollRow = 193
ActiveWindow.ScrollRow = 189
ActiveWindow.ScrollRow = 183
ActiveWindow.ScrollRow = 179
ActiveWindow.ScrollRow = 176
ActiveWindow.ScrollRow = 168
ActiveWindow.ScrollRow = 164
ActiveWindow.ScrollRow = 159
ActiveWindow.ScrollRow = 155
ActiveWindow.ScrollRow = 149
ActiveWindow.ScrollRow = 146
ActiveWindow.ScrollRow = 142
ActiveWindow.ScrollRow = 136
ActiveWindow.ScrollRow = 129
ActiveWindow.ScrollRow = 125
ActiveWindow.ScrollRow = 119
ActiveWindow.ScrollRow = 116
ActiveWindow.ScrollRow = 110
ActiveWindow.ScrollRow = 104
ActiveWindow.ScrollRow = 101
ActiveWindow.ScrollRow = 95
ActiveWindow.ScrollRow = 87
ActiveWindow.ScrollRow = 82
ActiveWindow.ScrollRow = 76
ActiveWindow.ScrollRow = 72
ActiveWindow.ScrollRow = 69
ActiveWindow.ScrollRow = 65
ActiveWindow.ScrollRow = 61
ActiveWindow.ScrollRow = 57
ActiveWindow.ScrollRow = 54
ActiveWindow.ScrollRow = 52
ActiveWindow.ScrollRow = 50
ActiveWindow.ScrollRow = 48
ActiveWindow.ScrollRow = 46
ActiveWindow.ScrollRow = 44
ActiveWindow.ScrollRow = 42
ActiveWindow.ScrollRow = 40
ActiveWindow.ScrollRow = 39
ActiveWindow.ScrollRow = 37
ActiveWindow.ScrollRow = 33
ActiveWindow.ScrollRow = 31
ActiveWindow.ScrollRow = 25
ActiveWindow.ScrollRow = 24
ActiveWindow.ScrollRow = 20
ActiveWindow.ScrollRow = 16
ActiveWindow.ScrollRow = 14
ActiveWindow.ScrollRow = 10
ActiveWindow.ScrollRow = 9
ActiveWindow.ScrollRow = 7
ActiveWindow.ScrollRow = 5
ActiveWindow.ScrollRow = 3
ActiveWindow.ScrollRow = 1
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
Range("G2:G1000").Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlInsideVertical)
End With
With Selection.Borders(xlInsideHorizontal)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
Range("G5").Select
Selection.AutoFilter Field:=4, Criteria1:="<>dcm", Operator:=xlAnd
Range("G3:G599").Select
ActiveWindow.ScrollRow = 578
ActiveWindow.ScrollRow = 576
ActiveWindow.ScrollRow = 575
ActiveWindow.ScrollRow = 571
ActiveWindow.ScrollRow = 563
ActiveWindow.ScrollRow = 556
ActiveWindow.ScrollRow = 549
ActiveWindow.ScrollRow = 532
ActiveWindow.ScrollRow = 522
ActiveWindow.ScrollRow = 509
ActiveWindow.ScrollRow = 493
ActiveWindow.ScrollRow = 476
ActiveWindow.ScrollRow = 454
ActiveWindow.ScrollRow = 437
ActiveWindow.ScrollRow = 403
ActiveWindow.ScrollRow = 373
ActiveWindow.ScrollRow = 353
ActiveWindow.ScrollRow = 334
ActiveWindow.ScrollRow = 306
ActiveWindow.ScrollRow = 290
ActiveWindow.ScrollRow = 273
ActiveWindow.ScrollRow = 260
ActiveWindow.ScrollRow = 251
ActiveWindow.ScrollRow = 241
ActiveWindow.ScrollRow = 232
ActiveWindow.ScrollRow = 223
ActiveWindow.ScrollRow = 219
ActiveWindow.ScrollRow = 210
ActiveWindow.ScrollRow = 202
ActiveWindow.ScrollRow = 195
ActiveWindow.ScrollRow = 185
ActiveWindow.ScrollRow = 176
ActiveWindow.ScrollRow = 165
ActiveWindow.ScrollRow = 154
ActiveWindow.ScrollRow = 139
ActiveWindow.ScrollRow = 120
ActiveWindow.ScrollRow = 107
ActiveWindow.ScrollRow = 98
ActiveWindow.ScrollRow = 89
ActiveWindow.ScrollRow = 81
ActiveWindow.ScrollRow = 70
ActiveWindow.ScrollRow = 57
ActiveWindow.ScrollRow = 48
ActiveWindow.ScrollRow = 42
ActiveWindow.ScrollRow = 36
ActiveWindow.ScrollRow = 31
ActiveWindow.ScrollRow = 25
ActiveWindow.ScrollRow = 21
ActiveWindow.ScrollRow = 16
ActiveWindow.ScrollRow = 10
ActiveWindow.ScrollRow = 3
ActiveWindow.ScrollRow = 1
Selection.AutoFilter Field:=4
Range("A1:G1").Select
Selection.AutoFilter
Selection.AutoFilter
Selection.AutoFilter Field:=7, Criteria1:="#N/A"
Range("G603:G1000").Select
Selection.ClearContents
Selection.AutoFilter Field:=7
Selection.AutoFilter Field:=4, Criteria1:="<>DCM", Operator:=xlAnd
Range("G3:G1000").Select
Selection.ClearContents
Range("I8").Select
Selection.AutoFilter Field:=4
Range("G4").Select
End Sub
Sub DCMUsageSheet()
'
' DCMUsageSheet Macro
' Macro recorded 20/07/2005 by Manoj Manoharan
'
findSheet("DCM Usage Sheet").Activate
Range("B2").Select
ActiveCell.FormulaR1C1 = "=COUNTIF(depnames,""PFG CENTRAL EXP"")"
Range("B3").Select
ActiveCell.FormulaR1C1 = "=COUNTIF(depnames,""SECONDARY LOAN TRADING 4"")"
Range("B4").Select
ActiveCell.FormulaR1C1 = "=COUNTIF(depnames,""CONDUITS 4"")"
Range("B5").Select
ActiveCell.FormulaR1C1 = "=SUM(COUNTIF(depnames,""UK HIGH YIELD CORP FINANCE 4""),COUNTIF(depnames,""FS 4""))"
Range("B6").Select
ActiveCell.FormulaR1C1 = "=COUNTIF(depnames,""FIGRE 44289"")"
Range("B7").Select
ActiveCell.FormulaR1C1 = "=COUNTIF(depnames,""GENERAL INDUSTRIES 4"")"
Range("B8").Select
ActiveCell.FormulaR1C1 = "=SUM(COUNTIF(depnames,""DCM CO LOAN CAPITAL MARKETS 44""),COUNTIF(depnames,""DCM CO LOAN DISTR DESK 4""))"
Range("B9").Select
ActiveCell.FormulaR1C1 = "=COUNTIF(depnames,""LONDON CAPITAL MARKETS 44289"")"
Range("B10").Select
ActiveCell.FormulaR1C1 = "=COUNTIF(depnames,""RATINGS ADVISORY 44289"")"
Range("B11").Select
ActiveCell.FormulaR1C1 = "=COUNTIF(depnames,""EMEA DCM MGMT-H-CHASE 4"")"
Range("B12").Select
ActiveCell.FormulaR1C1 = "=COUNTIF(depnames,""JPSPRD EUR-BNK063723516UK 4428"")"
Range("B13").Select
ActiveCell.FormulaR1C1 = "=COUNTIF(depnames,""JPRESF 063726578UK 4"")"
Range("B14").Select
ActiveCell.FormulaR1C1 = "=COUNTIF(depnames,""JPEM EU CAP MK063703207UK 4428"")"
Range("B15").Select
ActiveCell.FormulaR1C1 = "=COUNTIF(depnames,""JPEURP CREDT A063726676UK 4428"")"
Range("B16").Select
ActiveCell.FormulaR1C1 = "=COUNTIF(depnames,""CAZ JV DMC"")"
Range("B17").Select
ActiveCell.FormulaR1C1 = "=COUNTIF(depnames,""Other"")"
Range("B18").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-16]C:R[-1]C)"
Range("C2").Select
ActiveCell.FormulaR1C1 = _
"=SUM(IF(depnames=""PFG CENTRAL EXP"", IF('User Stats'!RC[2]:R[998]C[2]>0,1,0)))"
Range("C3").Select
Selection.FormulaArray = _
"=SUM(IF(depnames=""SECONDARY LOAN TRADING 4"",IF('User Stats'!R[-1]C5:R[997]C5>0,1,0)))"
Range("C4").Select
Selection.FormulaArray = _
"=SUM(IF(depnames=""CONDUITS 4"",IF('User Stats'!R[-2]C5:R[996]C5>0,1,0)))"
Range("C5").Select
Selection.FormulaArray = _
"=SUM(IF(depnames=""UK HIGH YIELD CORP FINANCE 4"",IF('User Stats'!R[-3]C5:R[995]C5>0,1,0)))+SUM(IF(depnames=""FS 4"",IF('User Stats'!R[-3]C5:R[995]C5>0,1,0)))"
Range("C6").Select
Selection.FormulaArray = _
"=SUM(IF(depnames=""FIGRE 44289"",IF('User Stats'!R[-4]C5:R[994]C5>0,1,0)))"
Range("C7").Select
Selection.FormulaArray = _
"=SUM(IF(depnames=""GENERAL INDUSTRIES 4"",IF('User Stats'!R[-5]C5:R[993]C5>0,1,0)))"
Range("C8").Select
Selection.FormulaArray = _
"=SUM(IF(depnames=""DCM CO LOAN CAPITAL MARKETS 44"",IF('User Stats'!R[-6]C5:R[992]C5>0,1,0)))+SUM(IF(depnames=""DCM CO LOAN DISTR DESK 4"",IF('User Stats'!R[-6]C5:R[992]C5>0,1,0)))"
Range("C9").Select
Selection.FormulaArray = _
"=SUM(IF(depnames=""LONDON CAPITAL MARKETS 44289"",IF('User Stats'!R[-7]C5:R[991]C5>0,1,0)))"
Range("C10").Select
Selection.FormulaArray = _
"=SUM(IF(depnames=""RATINGS ADVISORY 44289"",IF('User Stats'!R[-8]C5:R[990]C5>0,1,0)))"
Range("C11").Select
Selection.FormulaArray = _
"=SUM(IF(depnames=""EMEA DCM MGMT-H-CHASE 4"",IF('User Stats'!R[-9]C5:R[989]C5>0,1,0)))"
Range("C11").Select
Selection.FormulaArray = _
"=SUM(IF(depnames=""EMEA DCM MGMT-H-CHASE 4"",IF('User Stats'!R[-9]C5:R[989]C5>0,1,0)))"
Range("C12").Select
Selection.FormulaArray = _
"=SUM(IF(depnames=""JPSPRD EUR-BNK063723516UK 4428"",IF('User Stats'!R[-10]C5:R[988]C5>0,1,0)))"
Range("C13").Select
Selection.FormulaArray = _
"=SUM(IF(depnames=""JPRESF 063726578UK 4"",IF('User Stats'!R[-11]C5:R[987]C5>0,1,0)))"
Range("C14").Select
Selection.FormulaArray = _
"=SUM(IF(depnames=""JPEM EU CAP MK063703207UK 4428"",IF('User Stats'!R[-12]C5:R[986]C5>0,1,0)))"
Range("C15").Select
ActiveCell.FormulaR1C1 = _
"=SUM(IF(depnames=""JPEURP CREDT A063726676UK 4428"",IF('User Stats'!R[-13]C5:R[985]C5>0,1,0)))"
Range("C16").Select
Selection.FormulaArray = _
"=SUM(IF(depnames=""CAZ JV DMC"",IF('User Stats'!R[-14]C5:R[984]C5>0,1,0)))"
Range("C17").Select
Selection.FormulaArray = _
"=SUM(IF(depnames=""Other"",IF('User Stats'!R[-15]C5:R[983]C5>0,1,0)))"
Range("C18").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-16]C:R[-1]C)"
Range("D2").Select
ActiveCell.FormulaR1C1 = "=RC[-1]/RC[-2]"
Range("D3").Select
ActiveCell.FormulaR1C1 = "=RC[-1]/RC[-2]"
Range("D4").Select
ActiveCell.FormulaR1C1 = "=RC[-1]/RC[-2]"
Range("D5").Select
ActiveCell.FormulaR1C1 = "=RC[-1]/RC[-2]"
Range("D6").Select
ActiveCell.FormulaR1C1 = "=RC[-1]/RC[-2]"
Range("D7").Select
ActiveCell.FormulaR1C1 = "=RC[-1]/RC[-2]"
Range("D8").Select
ActiveCell.FormulaR1C1 = "=RC[-1]/RC[-2]"
Range("D9").Select
ActiveCell.FormulaR1C1 = "=RC[-1]/RC[-2]"
Range("D10").Select
ActiveCell.FormulaR1C1 = "=RC[-1]/RC[-2]"
Range("D11").Select
ActiveCell.FormulaR1C1 = "=RC[-1]/RC[-2]"
Range("D12").Select
ActiveCell.FormulaR1C1 = "=RC[-1]/RC[-2]"
Range("D13").Select
ActiveCell.FormulaR1C1 = "=RC[-1]/RC[-2]"
Range("D14").Select
ActiveCell.FormulaR1C1 = "=RC[-1]/RC[-2]"
Range("D15").Select
ActiveCell.FormulaR1C1 = "=RC[-1]/RC[-2]"
Range("D16").Select
ActiveCell.FormulaR1C1 = "=RC[-1]/RC[-2]"
Range("D17").Select
ActiveCell.FormulaR1C1 = "=RC[-1]/RC[-2]"
Range("D18").Select
ActiveCell.FormulaR1C1 = "=RC[-1]/RC[-2]"
Range("E2").Select
Selection.FormulaArray = _
"=SUM(IF(depnames=""PFG CENTRAL EXP"", IF('User Stats'!RC[1]:R[998]C[1]>0,1,0)))"
Range("E3").Select
Selection.FormulaArray = _
"=SUM(IF(depnames=""SECONDARY LOAN TRADING 4"",IF('User Stats'!R[-1]C6:R[997]C6>0,1,0)))"
Range("E4").Select
Selection.FormulaArray = _
"=SUM(IF(depnames=""CONDUITS 4"",IF('User Stats'!R[-2]C6:R[996]C6>0,1,0)))"
Range("E5").Select
Selection.FormulaArray = _
"=SUM(IF(depnames=""UK HIGH YIELD CORP FINANCE 4"",IF('User Stats'!R[-3]C6:R[995]C6>0,1,0)))+SUM(IF(depnames=""FS 4"",IF('User Stats'!R[-3]C6:R[995]C6>0,1,0)))"
Range("E6").Select
Selection.FormulaArray = _
"=SUM(IF(depnames=""FIGRE 44289"",IF('User Stats'!R[-4]C6:R[994]C6>0,1,0)))"
Range("E7").Select
Selection.FormulaArray = _
"=SUM(IF(depnames=""GENERAL INDUSTRIES 4"",IF('User Stats'!R[-5]C6:R[993]C6>0,1,0)))"
Range("E8").Select
Selection.FormulaArray = _
"=SUM(IF(depnames=""DCM CO LOAN CAPITAL MARKETS 44"",IF('User Stats'!R[-6]C6:R[992]C6>0,1,0)))+SUM(IF(depnames=""DCM CO LOAN DISTR DESK 4"",IF('User Stats'!R[-6]C6:R[992]C6>0,1,0)))"
Range("E9").Select
Selection.FormulaArray = _
"=SUM(IF(depnames=""LONDON CAPITAL MARKETS 44289"",IF('User Stats'!R[-7]C6:R[991]C6>0,1,0)))"
Range("E10").Select
Selection.FormulaArray = _
"=SUM(IF(depnames=""RATINGS ADVISORY 44289"",IF('User Stats'!R[-8]C6:R[990]C6>0,1,0)))"
Range("E11").Select
Selection.FormulaArray = _
"=SUM(IF(depnames=""EMEA DCM MGMT-H-CHASE 4"",IF('User Stats'!R[-9]C6:R[989]C6>0,1,0)))"
Range("E12").Select
Selection.FormulaArray = _
"=SUM(IF(depnames=""JPSPRD EUR-BNK063723516UK 4428"",IF('User Stats'!R[-10]C6:R[988]C6>0,1,0)))"
Range("E13").Select
Selection.FormulaArray = _
"=SUM(IF(depnames=""JPRESF 063726578UK 4"",IF('User Stats'!R[-11]C6:R[987]C6>0,1,0)))"
Range("E14").Select
Selection.FormulaArray = _
"=SUM(IF(depnames=""JPEM EU CAP MK063703207UK 4428"",IF('User Stats'!R[-12]C6:R[986]C6>0,1,0)))"
Range("E15").Select
Selection.FormulaArray = _
"=SUM(IF(depnames=""JPEURP CREDT A063726676UK 4428"",IF('User Stats'!R[-13]C6:R[985]C6>0,1,0)))"
Range("E16").Select
Selection.FormulaArray = _
"=SUM(IF(depnames=""CAZ JV DMC"",IF('User Stats'!R[-14]C6:R[984]C6>0,1,0)))"
Range("E17").Select
Selection.FormulaArray = _
"=SUM(IF(depnames=""Other"",IF('User Stats'!R[-15]C6:R[983]C6>0,1,0)))"
Range("E18").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-16]C:R[-1]C)"
Range("F2").Select
ActiveCell.FormulaR1C1 = "=RC[-1]/RC[-4]"
Range("F3").Select
ActiveCell.FormulaR1C1 = "=RC[-1]/RC[-4]"
Range("F4").Select
ActiveCell.FormulaR1C1 = "=RC[-1]/RC[-4]"
Range("F5").Select
ActiveCell.FormulaR1C1 = "=RC[-1]/RC[-4]"
Range("F6").Select
ActiveCell.FormulaR1C1 = "=RC[-1]/RC[-4]"
Range("F7").Select
ActiveCell.FormulaR1C1 = "=RC[-1]/RC[-4]"
Range("F8").Select
ActiveCell.FormulaR1C1 = "=RC[-1]/RC[-4]"
Range("F9").Select
ActiveCell.FormulaR1C1 = "=RC[-1]/RC[-4]"
Range("F10").Select
ActiveCell.FormulaR1C1 = "=RC[-1]/RC[-4]"
Range("F11").Select
ActiveCell.FormulaR1C1 = "=RC[-1]/RC[-4]"
Range("F12").Select
ActiveCell.FormulaR1C1 = "=RC[-1]/RC[-4]"
Range("F13").Select
ActiveCell.FormulaR1C1 = "=RC[-1]/RC[-4]"
Range("F14").Select
ActiveCell.FormulaR1C1 = "=RC[-1]/RC[-4]"
Range("F15").Select
ActiveCell.FormulaR1C1 = "=RC[-1]/RC[-4]"
Range("F16").Select
ActiveCell.FormulaR1C1 = "=RC[-1]/RC[-4]"
Range("F17").Select
ActiveCell.FormulaR1C1 = "=RC[-1]/RC[-4]"
Range("F18").Select
ActiveCell.FormulaR1C1 = "=RC[-1]/RC[-4]"
Range("B2:F17").Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlInsideVertical)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
Range("F20").Select
End Sub
I didn't think it was worth just dropping in a few bits of the code as it might be clearer with the whole lot.Basically i am quite slow when it comes to what exactly is being said in programming code.
The result when running a command button is i get a #NAME? error.I have highlighted where abouts the code where it seems to be going wrong. This is not my code and hence not sure what is going on with it.Technically the code should probably be correct (as it has run before) but when i have opened the s/s where the below code lives in and the s/s it refers to Employee.csv and name the range "emea" in that s/s, run the command button it runs perfectly but for this odd supplementary column code where it is adding "Other" rather than code Department Names it is meant to be reading from the Employee.csv file. I know this is an airy thread but i thought someone might spot something stupid which i am or am not doing. Cheers
Sub ArrangeData()
'
' ArrangeData Macro
' Macro recorded 27/06/2005 by Manoj
'
findSheet("User Stats").Activate
Columns("C:C").Select
Selection.Delete Shift:=xlToLeft
Columns("D
").SelectSelection.Insert Shift:=xlToRight
Rows("1:1").Select
Selection.Insert Shift:=xlDown
Range("A1").Select
ActiveCell.FormulaR1C1 = "User Name"
Range("B1").Select
ActiveCell.FormulaR1C1 = "SID"
Range("C1").Select
ActiveCell.FormulaR1C1 = "Documentum Group"
Range("D1").Select
ActiveCell.FormulaR1C1 = "Top Level Group"
Range("E1").Select
ActiveCell.FormulaR1C1 = "Usage This Week"
Range("F1").Select
ActiveCell.FormulaR1C1 = "Usage Till Date"
Cells.Select
Cells.EntireRow.AutoFit
Range("F2").Select
Columns("A:A").ColumnWidth = 8.57
Columns("A:A").EntireColumn.AutoFit
Columns("B:B").EntireColumn.AutoFit
Columns("C:C").EntireColumn.AutoFit
Columns("D
").EntireColumn.AutoFitColumns("E:E").EntireColumn.AutoFit
Columns("F:F").EntireColumn.AutoFit
Columns("A:A").ColumnWidth = 13.43
Cells.Select
Cells.EntireRow.AutoFit
Range("A1:F1").Select
Selection.Font.Bold = True
Range("A1:F1").Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlInsideVertical)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlInsideHorizontal)
End With
Range("A1:F1").Select
Selection.AutoFilter
Range("D2
1000").SelectActiveWorkbook.Names.Add Name:="groups", RefersToR1C1:= _
"='User stats'!R2C4:R1000C4"
Range("C2
1000").SelectActiveWorkbook.Names.Add Name:="subgroups", RefersToR1C1:= _
"='User stats'!R2C3:R1000C3"
End Sub
Public Sub TopLevelSort()
Dim currentSheet
Set currentSheet = findSheet("User Stats")
Dim index As Integer
Dim currentValue As String
index = 2
Do While index < 1000
'check the value
currentValue = currentSheet.Cells(index, 3)
If currentValue = "dcm" Then
currentSheet.Cells(index, 4) = "DCM"
ElseIf currentValue = "loan trading" Then
currentSheet.Cells(index, 4) = "DCM"
ElseIf currentValue = "dcm - leverage finance" Then
currentSheet.Cells(index, 4) = "DCM"
ElseIf currentValue = "corporate banking - corporates" Then
currentSheet.Cells(index, 4) = "GCRM"
ElseIf currentValue = "corporate banking - fig" Then
currentSheet.Cells(index, 4) = "GCRM"
ElseIf currentValue = "account admin" Then
currentSheet.Cells(index, 4) = "IT Support"
ElseIf currentValue = "ad build" Then
currentSheet.Cells(index, 4) = "IT Support"
ElseIf currentValue = "ad operate" Then
currentSheet.Cells(index, 4) = "IT Support"
ElseIf currentValue = "business power users" Then
currentSheet.Cells(index, 4) = "Business Power Users"
ElseIf currentValue = "corporate banking - fs" Then
currentSheet.Cells(index, 4) = "GCRM"
ElseIf currentValue = "credit executives" Then
currentSheet.Cells(index, 4) = "GCRM"
ElseIf currentValue = "dcm mo" Then
currentSheet.Cells(index, 4) = "DCM MO"
ElseIf currentValue = "desktop support" Then
currentSheet.Cells(index, 4) = "IT Support"
ElseIf currentValue = "desktop support new" Then
currentSheet.Cells(index, 4) = "IT Support"
ElseIf currentValue = "doc control group" Then
currentSheet.Cells(index, 4) = "Doc Control Group"
ElseIf currentValue = "gcrm" Then
currentSheet.Cells(index, 4) = "gcrm"
ElseIf currentValue = "gcrmo corps/fig support" Then
currentSheet.Cells(index, 4) = "GCRM MO"
ElseIf currentValue = "gcrmo funds support" Then
currentSheet.Cells(index, 4) = "GCRM MO"
ElseIf currentValue = "gcrmo projects" Then
currentSheet.Cells(index, 4) = "GCRM MO"
ElseIf currentValue = "gcrmo reporting" Then
currentSheet.Cells(index, 4) = "GCRM MO"
ElseIf currentValue = "gcrmo special credit" Then
currentSheet.Cells(index, 4) = "GCRM MO"
ElseIf currentValue = "loans agency" Then
currentSheet.Cells(index, 4) = "GCRM MO"
ElseIf currentValue = "ib credit review" Then
currentSheet.Cells(index, 4) = "GCRM"
ElseIf currentValue = "no group" Then
currentSheet.Cells(index, 4) = "No Group"
ElseIf currentValue = "presentations" Then
currentSheet.Cells(index, 4) = "Presentations"
ElseIf currentValue = "special credit" Then
currentSheet.Cells(index, 4) = "GCRM"
ElseIf currentValue = "team usa" Then
currentSheet.Cells(index, 4) = "GCRM MO"
ElseIf currentValue = "teg" Then
currentSheet.Cells(index, 4) = "TEG"
ElseIf currentValue = "teg admin" Then
currentSheet.Cells(index, 4) = "TEG Admin"
ElseIf currentValue = "bond p&l mo" Then
currentSheet.Cells(index, 4) = "DCM MO"
Else
End If
index = index + 1
Loop
End Sub
Sub GCRMUsageSheet()
'
' GCRMUsageSheet Macro
' Macro recorded 27/06/2005 by Manoj
'
'
findSheet("GCRM Usage").Activate
Range("B2").Select
ActiveCell.FormulaR1C1 = "=COUNTIF(groups,""=GCRM"")"
Range("B3").Select
ActiveCell.FormulaR1C1 = _
"=COUNTIF(subgroups,""=corporate banking - corporates"")"
Range("B4").Select
ActiveCell.FormulaR1C1 = "=COUNTIF(subgroups,""=corporate banking - fig"")"
Range("B5").Select
ActiveCell.FormulaR1C1 = "=COUNTIF(subgroups,""=corporate banking - fs"")"
Range("B6").Select
ActiveCell.FormulaR1C1 = "=COUNTIF(subgroups,""=credit executives"")"
Range("B7").Select
ActiveCell.FormulaR1C1 = "=COUNTIF(subgroups,""=special credit"")"
Range("B8").Select
ActiveCell.FormulaR1C1 = "=COUNTIF(subgroups,""=ib credit review"")"
Range("B9").Select
ActiveCell.FormulaR1C1 = "=COUNTIF(subgroups,""=gcrm"")"
Range("B11").Select
ActiveCell.FormulaR1C1 = "=COUNTIF(groups,""=GCRM MO"")"
Range("B12").Select
ActiveCell.FormulaR1C1 = "=COUNTIF(subgroups,""=gcrmo corps/fig support"")"
Range("B13").Select
ActiveCell.FormulaR1C1 = "=COUNTIF(subgroups,""=gcrmo funds support"")"
Range("B14").Select
ActiveCell.FormulaR1C1 = "=COUNTIF(subgroups,""=gcrmo reporting"")"
Range("B15").Select
ActiveCell.FormulaR1C1 = "=COUNTIF(subgroups,""=gcrmo projects"")"
Range("B16").Select
ActiveCell.FormulaR1C1 = "=COUNTIF(subgroups,""=team usa"")"
Range("B17").Select
ActiveCell.FormulaR1C1 = "=COUNTIF(subgroups,""=loans agency"")"
Range("C2").Select
Selection.FormulaArray = _
"=SUM(IF(groups=""gcrm"",IF('User Stats'!RC5:R[998]C5>0,1,0)))"
Range("C3").Select
Selection.FormulaArray = _
"=SUM(IF(subgroups=""corporate banking - corporates"",IF('User Stats'!R[-1]C5:R[997]C5>0,1,0)))"
Range("C4").Select
Selection.FormulaArray = _
"=SUM(IF(subgroups=""corporate banking - fig"",IF('User Stats'!R[-2]C5:R[996]C5>0,1,0)))"
Range("C5").Select
Selection.FormulaArray = _
"=SUM(IF(subgroups=""corporate banking - fs"",IF('User Stats'!R[-3]C5:R[995]C5>0,1,0)))"
Range("C6").Select
Selection.FormulaArray = _
"=SUM(IF(subgroups=""credit executives"",IF('User Stats'!R[-4]C5:R[994]C5>0,1,0)))"
Range("C7").Select
Selection.FormulaArray = _
"=SUM(IF(subgroups=""special credit"",IF('User Stats'!R[-5]C5:R[993]C5>0,1,0)))"
Range("C8").Select
Selection.FormulaArray = _
"=SUM(IF(subgroups=""ib credit review"",IF('User Stats'!R[-6]C5:R[992]C5>0,1,0)))"
Range("C9").Select
Selection.FormulaArray = _
"=SUM(IF(subgroups=""gcrm"",IF('User Stats'!R[-7]C5:R[991]C5>0,1,0)))"
Range("C11").Select
Selection.FormulaArray = _
"=SUM(IF(groups=""gcrm mo"",IF('User Stats'!R[-9]C5:R[989]C5>0,1,0)))"
Range("C12").Select
Selection.FormulaArray = _
"=SUM(IF(subgroups=""gcrmo corps/fig support"",IF('User Stats'!R[-10]C5:R[988]C5>0,1,0)))"
Range("C13").Select
Selection.FormulaArray = _
"=SUM(IF(subgroups=""gcrmo funds support"",IF('User Stats'!R[-11]C5:R[987]C5>0,1,0)))"
Range("C14").Select
Selection.FormulaArray = _
"=SUM(IF(subgroups=""gcrmo reporting"",IF('User Stats'!R[-12]C5:R[986]C5>0,1,0)))"
Range("C15").Select
Selection.FormulaArray = _
"=SUM(IF(subgroups=""gcrmo projects"",IF('User Stats'!R[-13]C5:R[985]C5>0,1,0)))"
Range("C16").Select
Selection.FormulaArray = _
"=SUM(IF(subgroups=""team usa"",IF('User Stats'!R[-14]C5:R[984]C5>0,1,0)))"
Range("C17").Select
Selection.FormulaArray = _
"=SUM(IF(subgroups=""loans agency"",IF('User Stats'!R[-15]C5:R[983]C5>0,1,0)))"
Range("D2").Select
ActiveCell.FormulaR1C1 = "=RC[-1]/RC[-2]"
Range("D3").Select
ActiveCell.FormulaR1C1 = "=RC[-1]/RC[-2]"
Range("D4").Select
ActiveCell.FormulaR1C1 = "=RC[-1]/RC[-2]"
Range("D5").Select
ActiveCell.FormulaR1C1 = "=RC[-1]/RC[-2]"
Range("D6").Select
ActiveCell.FormulaR1C1 = "=RC[-1]/RC[-2]"
Range("D7").Select
ActiveCell.FormulaR1C1 = "=RC[-1]/RC[-2]"
Range("D8").Select
ActiveCell.FormulaR1C1 = "=RC[-1]/RC[-2]"
Range("D9").Select
ActiveCell.FormulaR1C1 = "=RC[-1]/RC[-2]"
Range("D11").Select
ActiveCell.FormulaR1C1 = "=RC[-1]/RC[-2]"
Range("D12").Select
ActiveCell.FormulaR1C1 = "=RC[-1]/RC[-2]"
Range("D13").Select
ActiveCell.FormulaR1C1 = "=RC[-1]/RC[-2]"
Range("D14").Select
ActiveCell.FormulaR1C1 = "=RC[-1]/RC[-2]"
Range("D15").Select
ActiveCell.FormulaR1C1 = "=RC[-1]/RC[-2]"
Range("D16").Select
ActiveCell.FormulaR1C1 = "=RC[-1]/RC[-2]"
Range("D16").Select
Selection.Style = "Percent"
Range("D17").Select
ActiveCell.FormulaR1C1 = "=RC[-1]/RC[-2]"
Range("D17").Select
Selection.Style = "Percent"
Range("E2").Select
Selection.FormulaArray = _
"=SUM(IF(groups=""gcrm"",IF('User Stats'!RC6:R[998]C6>0,1,0)))"
Range("E3").Select
Selection.FormulaArray = _
"=SUM(IF(subgroups=""corporate banking - corporates"",IF('User Stats'!R[-1]C6:R[997]C6>0,1,0)))"
Range("E4").Select
Selection.FormulaArray = _
"=SUM(IF(subgroups=""corporate banking - fig"",IF('User Stats'!R[-2]C6:R[996]C6>0,1,0)))"
Range("E5").Select
Selection.FormulaArray = _
"=SUM(IF(subgroups=""corporate banking - fs"",IF('User Stats'!R[-3]C6:R[995]C6>0,1,0)))"
Range("E6").Select
Selection.FormulaArray = _
"=SUM(IF(subgroups=""credit executives"",IF('User Stats'!R[-4]C6:R[994]C6>0,1,0)))"
Range("E7").Select
Selection.FormulaArray = _
"=SUM(IF(subgroups=""special credit"",IF('User Stats'!R[-5]C6:R[993]C6>0,1,0)))"
Range("E8").Select
Selection.FormulaArray = _
"=SUM(IF(subgroups=""ib credit review"",IF('User Stats'!R[-6]C6:R[992]C6>0,1,0)))"
Range("E9").Select
Selection.FormulaArray = _
"=SUM(IF(subgroups=""gcrm"",IF('User Stats'!R[-7]C6:R[991]C6>0,1,0)))"
Range("E11").Select
Selection.FormulaArray = _
"=SUM(IF(groups=""gcrm mo"",IF('User Stats'!R[-9]C6:R[989]C6>0,1,0)))"
Range("E12").Select
Selection.FormulaArray = _
"=SUM(IF(subgroups=""gcrmo corps/fig support"",IF('User Stats'!R[-10]C6:R[988]C6>0,1,0)))"
Range("E13").Select
Selection.FormulaArray = _
"=SUM(IF(subgroups=""gcrmo funds support"",IF('User Stats'!R[-11]C6:R[987]C6>0,1,0)))"
Range("E14").Select
Selection.FormulaArray = _
"=SUM(IF(subgroups=""gcrmo reporting"",IF('User Stats'!R[-12]C6:R[986]C6>0,1,0)))"
Range("E15").Select
Selection.FormulaArray = _
"=SUM(IF(subgroups=""gcrmo projects"",IF('User Stats'!R[-13]C6:R[985]C6>0,1,0)))"
Range("E16").Select
Selection.FormulaArray = _
"=SUM(IF(subgroups=""team usa"",IF('User Stats'!R[-14]C6:R[984]C6>0,1,0)))"
Range("E17").Select
Selection.FormulaArray = _
"=SUM(IF(subgroups=""loans agency"",IF('User Stats'!R[-15]C6:R[983]C6>0,1,0)))"
Range("F2").Select
ActiveCell.FormulaR1C1 = "=RC[-1]/RC[-4]"
Range("F3").Select
ActiveCell.FormulaR1C1 = "=RC[-1]/RC[-4]"
Range("F4").Select
ActiveCell.FormulaR1C1 = "=RC[-1]/RC[-4]"
Range("F5").Select
ActiveCell.FormulaR1C1 = "=RC[-1]/RC[-4]"
Range("F6").Select
ActiveCell.FormulaR1C1 = "=RC[-1]/RC[-4]"
Range("F7").Select
ActiveCell.FormulaR1C1 = "=RC[-1]/RC[-4]"
Range("F8").Select
ActiveCell.FormulaR1C1 = "=RC[-1]/RC[-4]"
Range("F9").Select
ActiveCell.FormulaR1C1 = "=RC[-1]/RC[-4]"
Range("F11").Select
ActiveCell.FormulaR1C1 = "=RC[-1]/RC[-4]"
Range("F12").Select
ActiveCell.FormulaR1C1 = "=RC[-1]/RC[-4]"
Range("F13").Select
ActiveCell.FormulaR1C1 = "=RC[-1]/RC[-4]"
Range("F14").Select
ActiveCell.FormulaR1C1 = "=RC[-1]/RC[-4]"
Range("F15").Select
ActiveCell.FormulaR1C1 = "=RC[-1]/RC[-4]"
Range("F16").Select
ActiveCell.FormulaR1C1 = "=RC[-1]/RC[-4]"
Range("F16").Select
Selection.Style = "Percent"
Range("F17").Select
ActiveCell.FormulaR1C1 = "=RC[-1]/RC[-4]"
Range("F17").Select
Selection.Style = "Percent"
Range("D2").Select
ActiveCell.FormulaR1C1 = "=RC[-1]/RC[-2]"
Range("D3").Select
ActiveCell.FormulaR1C1 = "=RC[-1]/RC[-2]"
Range("D4").Select
ActiveCell.FormulaR1C1 = "=RC[-1]/RC[-2]"
Range("D5").Select
ActiveCell.FormulaR1C1 = "=RC[-1]/RC[-2]"
Range("D6").Select
ActiveCell.FormulaR1C1 = "=RC[-1]/RC[-2]"
Range("D7").Select
End Sub
Sub UsageSummary()
'
' UsageSummary Macro
' Macro recorded 27/06/2005 by Manoj
'
'
findSheet("Reporting").Activate
Range("C5").Select
ActiveCell.FormulaR1C1 = "=COUNTIF(groups,""=dcm"")"
Range("C6").Select
Selection.FormulaArray = _
"=SUM(IF(groups=""dcm"",IF('User Stats'!R[-4]C5:R[994]C5>0,1,0)))"
Range("C8").Select
ActiveCell.FormulaR1C1 = "=R[-2]C/R[-3]C"
Range("D5").Select
ActiveCell.FormulaR1C1 = "=COUNTIF(groups,""=GCRM"")"
Range("D6").Select
ActiveCell.FormulaR1C1 = _
"=SUM(IF(groups=""gcrm"",IF('User Stats'!R[-4]C5:R[994]C5>0,1,0)))"
Range("D6").Select
Selection.FormulaArray = _
"=SUM(IF(groups=""gcrm"",IF('User Stats'!R[-4]C5:R[994]C5>0,1,0)))"
Range("D8").Select
ActiveCell.FormulaR1C1 = "=R[-2]C/R[-3]C"
Range("E5").Select
ActiveCell.FormulaR1C1 = "=COUNTIF(groups,""=gcrm mo"")"
Range("E6").Select
Selection.FormulaArray = _
"=SUM(IF(groups=""gcrm mo"",IF('User Stats'!R[-4]C5:R[994]C5>0,1,0)))"
Range("E8").Select
ActiveCell.FormulaR1C1 = "=R[-2]C/R[-3]C"
Range("F5").Select
ActiveCell.FormulaR1C1 = "=COUNTIF(groups,""=dcm mo"")"
Range("F6").Select
Selection.FormulaArray = _
"=SUM(IF(groups=""dcm mo"",IF('User Stats'!R[-4]C5:R[994]C5>0,1,0)))"
Range("F8").Select
ActiveCell.FormulaR1C1 = "=R[-2]C/R[-3]C"
Range("G5").Select
ActiveCell.FormulaR1C1 = "=COUNTIF(groups,""=doc control group"")"
Range("G6").Select
Selection.FormulaArray = _
"=SUM(IF(groups=""doc control group"",IF('User Stats'!R[-4]C5:R[994]C5>0,1,0)))"
Range("G8").Select
ActiveCell.FormulaR1C1 = "=R[-2]C/R[-3]C"
Range("G12").Select
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 3
Range("H5").Select
ActiveCell.FormulaR1C1 = "=(COUNTIF(groups,""=TEG"")+COUNTIF(groups,""=Teg Admin""))"
Range("H6").Select
Selection.FormulaArray = _
"=(SUM(IF(groups=""teg"",IF('User Stats'!R[-4]C5:R[994]C5>0,1,0)))+SUM(IF(groups=""teg admin"",IF('User Stats'!R[-4]C5:R[994]C5>0,1,0))))"
Range("H8").Select
ActiveCell.FormulaR1C1 = "=R[-2]C/R[-3]C"
Range("I5").Select
ActiveCell.FormulaR1C1 = "=COUNTIF(groups,""=presentations"")"
Range("I6").Select
Selection.FormulaArray = _
"=SUM(IF(groups=""Presentations"",IF('User Stats'!R[-4]C5:R[994]C5>0,1,0)))"
Range("I8").Select
ActiveCell.FormulaR1C1 = "=R[-2]C/R[-3]C"
Range("J5").Select
ActiveCell.FormulaR1C1 = _
"=COUNTIF(groups,""=IT support"")+COUNTIF(groups,""=AD operate"")+COUNTIF(groups,""=AD build"")"
Range("J6").Select
Selection.FormulaArray = _
"=SUM(IF(groups=""IT Support"",IF('User Stats'!R[-4]C5:R[994]C5>0,1,0)))"
Range("J8").Select
ActiveCell.FormulaR1C1 = "=R[-2]C/R[-3]C"
Range("K5").Select
ActiveCell.FormulaR1C1 = "=COUNTIF(groups,""=business power users"")"
Range("K6").Select
Selection.FormulaArray = _
"=SUM(IF(groups=""business power users"",IF('User Stats'!R[-4]C5:R[994]C5>0,1,0)))"
Range("J6").Select
Selection.FormulaArray = _
"=SUM(IF(groups=""IT Support"",IF('User Stats'!R[-4]C5:R[994]C5>0,1,0)))"
Range("K8").Select
ActiveCell.FormulaR1C1 = "=R[-2]C/R[-3]C"
Range("L5").Select
ActiveCell.FormulaR1C1 = "=COUNTIF(groups,""=no group"")"
Range("L6").Select
Selection.FormulaArray = _
"=SUM(IF(groups=""no group"",IF('User Stats'!R[-4]C5:R[994]C5>0,1,0)))"
Range("L8").Select
ActiveCell.FormulaR1C1 = "=R[-2]C/R[-3]C"
Range("M5").Select
ActiveCell.FormulaR1C1 = "=SUM(RC[-10]:RC[-1])"
Range("M6").Select
ActiveCell.FormulaR1C1 = "=SUM(RC[-10]:RC[-1])"
Range("M8").Select
ActiveCell.FormulaR1C1 = "=R[-2]C/R[-3]C"
Range("M5:M6").Select
ActiveWindow.LargeScroll ToRight:=-1
Range("C5:M8").Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlInsideVertical)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlInsideHorizontal)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlInsideVertical)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlInsideHorizontal)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
Range("G14").Select
End Sub
Public Sub Omnibus()
Call Module1.ArrangeData
Call Module1.TopLevelSort
Call Module1.UsageSummary
Call Module1.GCRMUsageSheet
Call Module1.FindDCMSubDept
Call Module1.OtherCalc
Call Module1.DCMUsageSheet
End Sub
'Finds a worksheet based on the sheetName (WARNING CASE-SENSITIVE!), returns NOTHING if not found
'i.e. use IsEmpty() to check
Public Function findSheet(sheetName As String) As Worksheet
Dim index As Integer
Dim currentSheet As Worksheet
index = 1
Do While (index <= ActiveWorkbook.Sheets.Count)
Set currentSheet = ActiveWorkbook.Sheets(index)
If (currentSheet.Name = sheetName) Then
Set findSheet = currentSheet
Exit Function
End If
index = index + 1
Loop
End Function
Public Sub findSheetTest()
If (IsEmpty(findSheet("manojlikeshisdancing"))) Then
MsgBox ("EMPTY TEST DISGRACE!")
End
ElseIf (findSheet("User Stats").Name <> "User Stats") Then
MsgBox ("SHOCK SEARCH SCANDAL, Failure on name test [User Stats]")
End
Else
MsgBox "FINDSHEET BID SUCCESS!"
End If
End Sub
******************************************************
I THINK THIS IS THE PROCEDURE WHICH IS DUMPING THE INCORRECT VALUES INTO THE "G" COLUMN
Public Sub OtherCalc()
Dim currentSheet
Set currentSheet = findSheet("User Stats")
Dim index As Integer
findSheet("User Stats").Activate
'd.Add "Conduits", "4"
'd.Add "key", "1"
Dim d, i 'Create some variables
Set d = CreateObject("Scripting.Dictionary")
d.Add "PFG CENTRAL EXP", "1" 'Add some keys and items.
d.Add "SECONDARY LOAN TRADING 4", "1"
d.Add "CONDUITS 4", "1"
d.Add "UK HIGH YIELD CORP FINANCE 4", "1"
d.Add "FS 4", "1"
d.Add "FIGRE 44289", "6"
d.Add "GENERAL INDUSTRIES 4", "1"
d.Add "DCM CO LOAN CAPITAL MARKETS 44", "1"
d.Add "DCM CO LOAN DISTR DESK 4", "1"
d.Add "LONDON CAPITAL MARKETS 44289", "1"
d.Add "RATINGS ADVISORY 44289", "1"
d.Add "EMEA DCM MGMT-H-CHASE 4", "1"
d.Add "JPSPRD EUR-BNK063723516UK 4428", "1"
d.Add "JPRESF 063726578UK 4", "1"
d.Add "JPEM EU CAP MK063703207UK 4428", "1"
d.Add "JPEURP CREDT A063726676UK 4428", "1"
d.Add "CAZ JV DMC", "18"
index = 1
Do While index < 1000
If (currentSheet.Cells(index, 4)) = "DCM" Then
If Not (d.Exists(currentSheet.Cells(index, 7).Value)) Then
currentSheet.Cells(index, 7) = "Other"
End If
End If
index = index + 1
Loop
End Sub
*******************************************************
Sub FindDCMSubDept()
'
' FindDCMSubDept Macro
' Macro recorded 20/07/2005 by Manoj Manoharan
'
'
findSheet("User Stats").Activate
Range("G1").Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
Selection.Borders(xlEdgeTop).LineStyle = xlNone
Selection.Borders(xlEdgeBottom).LineStyle = xlNone
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
Selection.Borders(xlInsideVertical).LineStyle = xlNone
Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
ActiveCell.FormulaR1C1 = "E-Source Department Name"
With ActiveCell.Characters(Start:=1, Length:=24).Font
.Name = "Arial"
.FontStyle = "Bold"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
End With
Range("A1:G1").Select
Range("G1").Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
Range("G2").Select
Application.Goto Reference:="depnames"
ActiveWindow.ScrollRow = 3
ActiveWindow.ScrollRow = 1
ActiveWorkbook.Names("depnames").Delete
ActiveWorkbook.Names("usernames").Delete
Range("G2").Select
ActiveWorkbook.Names.Add Name:="depnames", RefersToR1C1:= _
"='User Stats'!R2C7:R1000C7"
ActiveWorkbook.Names.Add Name:="usernames", RefersToR1C1:= _
"='User Stats'!R2C1:R1000C1"
ActiveWorkbook.Names.Add Name:="usernames", RefersToR1C1:= _
"='User Stats'!R2C1:R1000C1"
Application.Goto Reference:="usernames"
ActiveWindow.ScrollRow = 1
ActiveWindow.ScrollRow = 3
ActiveWindow.ScrollRow = 5
ActiveWindow.ScrollRow = 10
ActiveWindow.ScrollRow = 14
ActiveWindow.ScrollRow = 18
ActiveWindow.ScrollRow = 23
ActiveWindow.ScrollRow = 31
ActiveWindow.ScrollRow = 40
ActiveWindow.ScrollRow = 48
ActiveWindow.ScrollRow = 55
ActiveWindow.ScrollRow = 64
ActiveWindow.ScrollRow = 72
ActiveWindow.ScrollRow = 83
ActiveWindow.ScrollRow = 89
ActiveWindow.ScrollRow = 100
ActiveWindow.ScrollRow = 107
ActiveWindow.ScrollRow = 118
ActiveWindow.ScrollRow = 124
ActiveWindow.ScrollRow = 128
ActiveWindow.ScrollRow = 130
ActiveWindow.ScrollRow = 131
ActiveWindow.ScrollRow = 130
ActiveWindow.ScrollRow = 128
ActiveWindow.ScrollRow = 124
ActiveWindow.ScrollRow = 116
ActiveWindow.ScrollRow = 107
ActiveWindow.ScrollRow = 94
ActiveWindow.ScrollRow = 83
ActiveWindow.ScrollRow = 74
ActiveWindow.ScrollRow = 62
ActiveWindow.ScrollRow = 51
ActiveWindow.ScrollRow = 38
ActiveWindow.ScrollRow = 29
ActiveWindow.ScrollRow = 16
ActiveWindow.ScrollRow = 5
ActiveWindow.ScrollRow = 1
Range("G2").Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(User Name,Employee.csv!emea,31,FALSE)"
Range("G2").Select
Selection.AutoFill Destination:=Range("G2:G1000"), Type:=xlFillDefault
Range("G2:G1000").Select
ActiveWindow.ScrollRow = 981
ActiveWindow.ScrollRow = 979
ActiveWindow.ScrollRow = 977
ActiveWindow.ScrollRow = 975
ActiveWindow.ScrollRow = 974
ActiveWindow.ScrollRow = 972
ActiveWindow.ScrollRow = 968
ActiveWindow.ScrollRow = 964
ActiveWindow.ScrollRow = 960
ActiveWindow.ScrollRow = 957
ActiveWindow.ScrollRow = 951
ActiveWindow.ScrollRow = 940
ActiveWindow.ScrollRow = 932
ActiveWindow.ScrollRow = 921
ActiveWindow.ScrollRow = 910
ActiveWindow.ScrollRow = 897
ActiveWindow.ScrollRow = 885
ActiveWindow.ScrollRow = 872
ActiveWindow.ScrollRow = 850
ActiveWindow.ScrollRow = 829
ActiveWindow.ScrollRow = 810
ActiveWindow.ScrollRow = 797
ActiveWindow.ScrollRow = 782
ActiveWindow.ScrollRow = 761
ActiveWindow.ScrollRow = 746
ActiveWindow.ScrollRow = 735
ActiveWindow.ScrollRow = 716
ActiveWindow.ScrollRow = 701
ActiveWindow.ScrollRow = 688
ActiveWindow.ScrollRow = 677
ActiveWindow.ScrollRow = 669
ActiveWindow.ScrollRow = 658
ActiveWindow.ScrollRow = 651
ActiveWindow.ScrollRow = 643
ActiveWindow.ScrollRow = 632
ActiveWindow.ScrollRow = 624
ActiveWindow.ScrollRow = 615
ActiveWindow.ScrollRow = 607
ActiveWindow.ScrollRow = 596
ActiveWindow.ScrollRow = 583
ActiveWindow.ScrollRow = 570
ActiveWindow.ScrollRow = 561
ActiveWindow.ScrollRow = 544
ActiveWindow.ScrollRow = 534
ActiveWindow.ScrollRow = 521
ActiveWindow.ScrollRow = 502
ActiveWindow.ScrollRow = 489
ActiveWindow.ScrollRow = 476
ActiveWindow.ScrollRow = 461
ActiveWindow.ScrollRow = 450
ActiveWindow.ScrollRow = 440
ActiveWindow.ScrollRow = 427
ActiveWindow.ScrollRow = 414
ActiveWindow.ScrollRow = 405
ActiveWindow.ScrollRow = 397
ActiveWindow.ScrollRow = 392
ActiveWindow.ScrollRow = 382
ActiveWindow.ScrollRow = 377
ActiveWindow.ScrollRow = 369
ActiveWindow.ScrollRow = 363
ActiveWindow.ScrollRow = 356
ActiveWindow.ScrollRow = 350
ActiveWindow.ScrollRow = 343
ActiveWindow.ScrollRow = 335
ActiveWindow.ScrollRow = 324
ActiveWindow.ScrollRow = 316
ActiveWindow.ScrollRow = 307
ActiveWindow.ScrollRow = 301
ActiveWindow.ScrollRow = 296
ActiveWindow.ScrollRow = 292
ActiveWindow.ScrollRow = 286
ActiveWindow.ScrollRow = 281
ActiveWindow.ScrollRow = 275
ActiveWindow.ScrollRow = 270
ActiveWindow.ScrollRow = 262
ActiveWindow.ScrollRow = 253
ActiveWindow.ScrollRow = 245
ActiveWindow.ScrollRow = 239
ActiveWindow.ScrollRow = 234
ActiveWindow.ScrollRow = 230
ActiveWindow.ScrollRow = 221
ActiveWindow.ScrollRow = 215
ActiveWindow.ScrollRow = 208
ActiveWindow.ScrollRow = 200
ActiveWindow.ScrollRow = 193
ActiveWindow.ScrollRow = 189
ActiveWindow.ScrollRow = 183
ActiveWindow.ScrollRow = 179
ActiveWindow.ScrollRow = 176
ActiveWindow.ScrollRow = 168
ActiveWindow.ScrollRow = 164
ActiveWindow.ScrollRow = 159
ActiveWindow.ScrollRow = 155
ActiveWindow.ScrollRow = 149
ActiveWindow.ScrollRow = 146
ActiveWindow.ScrollRow = 142
ActiveWindow.ScrollRow = 136
ActiveWindow.ScrollRow = 129
ActiveWindow.ScrollRow = 125
ActiveWindow.ScrollRow = 119
ActiveWindow.ScrollRow = 116
ActiveWindow.ScrollRow = 110
ActiveWindow.ScrollRow = 104
ActiveWindow.ScrollRow = 101
ActiveWindow.ScrollRow = 95
ActiveWindow.ScrollRow = 87
ActiveWindow.ScrollRow = 82
ActiveWindow.ScrollRow = 76
ActiveWindow.ScrollRow = 72
ActiveWindow.ScrollRow = 69
ActiveWindow.ScrollRow = 65
ActiveWindow.ScrollRow = 61
ActiveWindow.ScrollRow = 57
ActiveWindow.ScrollRow = 54
ActiveWindow.ScrollRow = 52
ActiveWindow.ScrollRow = 50
ActiveWindow.ScrollRow = 48
ActiveWindow.ScrollRow = 46
ActiveWindow.ScrollRow = 44
ActiveWindow.ScrollRow = 42
ActiveWindow.ScrollRow = 40
ActiveWindow.ScrollRow = 39
ActiveWindow.ScrollRow = 37
ActiveWindow.ScrollRow = 33
ActiveWindow.ScrollRow = 31
ActiveWindow.ScrollRow = 25
ActiveWindow.ScrollRow = 24
ActiveWindow.ScrollRow = 20
ActiveWindow.ScrollRow = 16
ActiveWindow.ScrollRow = 14
ActiveWindow.ScrollRow = 10
ActiveWindow.ScrollRow = 9
ActiveWindow.ScrollRow = 7
ActiveWindow.ScrollRow = 5
ActiveWindow.ScrollRow = 3
ActiveWindow.ScrollRow = 1
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
Range("G2:G1000").Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlInsideVertical)
End With
With Selection.Borders(xlInsideHorizontal)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
Range("G5").Select
Selection.AutoFilter Field:=4, Criteria1:="<>dcm", Operator:=xlAnd
Range("G3:G599").Select
ActiveWindow.ScrollRow = 578
ActiveWindow.ScrollRow = 576
ActiveWindow.ScrollRow = 575
ActiveWindow.ScrollRow = 571
ActiveWindow.ScrollRow = 563
ActiveWindow.ScrollRow = 556
ActiveWindow.ScrollRow = 549
ActiveWindow.ScrollRow = 532
ActiveWindow.ScrollRow = 522
ActiveWindow.ScrollRow = 509
ActiveWindow.ScrollRow = 493
ActiveWindow.ScrollRow = 476
ActiveWindow.ScrollRow = 454
ActiveWindow.ScrollRow = 437
ActiveWindow.ScrollRow = 403
ActiveWindow.ScrollRow = 373
ActiveWindow.ScrollRow = 353
ActiveWindow.ScrollRow = 334
ActiveWindow.ScrollRow = 306
ActiveWindow.ScrollRow = 290
ActiveWindow.ScrollRow = 273
ActiveWindow.ScrollRow = 260
ActiveWindow.ScrollRow = 251
ActiveWindow.ScrollRow = 241
ActiveWindow.ScrollRow = 232
ActiveWindow.ScrollRow = 223
ActiveWindow.ScrollRow = 219
ActiveWindow.ScrollRow = 210
ActiveWindow.ScrollRow = 202
ActiveWindow.ScrollRow = 195
ActiveWindow.ScrollRow = 185
ActiveWindow.ScrollRow = 176
ActiveWindow.ScrollRow = 165
ActiveWindow.ScrollRow = 154
ActiveWindow.ScrollRow = 139
ActiveWindow.ScrollRow = 120
ActiveWindow.ScrollRow = 107
ActiveWindow.ScrollRow = 98
ActiveWindow.ScrollRow = 89
ActiveWindow.ScrollRow = 81
ActiveWindow.ScrollRow = 70
ActiveWindow.ScrollRow = 57
ActiveWindow.ScrollRow = 48
ActiveWindow.ScrollRow = 42
ActiveWindow.ScrollRow = 36
ActiveWindow.ScrollRow = 31
ActiveWindow.ScrollRow = 25
ActiveWindow.ScrollRow = 21
ActiveWindow.ScrollRow = 16
ActiveWindow.ScrollRow = 10
ActiveWindow.ScrollRow = 3
ActiveWindow.ScrollRow = 1
Selection.AutoFilter Field:=4
Range("A1:G1").Select
Selection.AutoFilter
Selection.AutoFilter
Selection.AutoFilter Field:=7, Criteria1:="#N/A"
Range("G603:G1000").Select
Selection.ClearContents
Selection.AutoFilter Field:=7
Selection.AutoFilter Field:=4, Criteria1:="<>DCM", Operator:=xlAnd
Range("G3:G1000").Select
Selection.ClearContents
Range("I8").Select
Selection.AutoFilter Field:=4
Range("G4").Select
End Sub
Sub DCMUsageSheet()
'
' DCMUsageSheet Macro
' Macro recorded 20/07/2005 by Manoj Manoharan
'
findSheet("DCM Usage Sheet").Activate
Range("B2").Select
ActiveCell.FormulaR1C1 = "=COUNTIF(depnames,""PFG CENTRAL EXP"")"
Range("B3").Select
ActiveCell.FormulaR1C1 = "=COUNTIF(depnames,""SECONDARY LOAN TRADING 4"")"
Range("B4").Select
ActiveCell.FormulaR1C1 = "=COUNTIF(depnames,""CONDUITS 4"")"
Range("B5").Select
ActiveCell.FormulaR1C1 = "=SUM(COUNTIF(depnames,""UK HIGH YIELD CORP FINANCE 4""),COUNTIF(depnames,""FS 4""))"
Range("B6").Select
ActiveCell.FormulaR1C1 = "=COUNTIF(depnames,""FIGRE 44289"")"
Range("B7").Select
ActiveCell.FormulaR1C1 = "=COUNTIF(depnames,""GENERAL INDUSTRIES 4"")"
Range("B8").Select
ActiveCell.FormulaR1C1 = "=SUM(COUNTIF(depnames,""DCM CO LOAN CAPITAL MARKETS 44""),COUNTIF(depnames,""DCM CO LOAN DISTR DESK 4""))"
Range("B9").Select
ActiveCell.FormulaR1C1 = "=COUNTIF(depnames,""LONDON CAPITAL MARKETS 44289"")"
Range("B10").Select
ActiveCell.FormulaR1C1 = "=COUNTIF(depnames,""RATINGS ADVISORY 44289"")"
Range("B11").Select
ActiveCell.FormulaR1C1 = "=COUNTIF(depnames,""EMEA DCM MGMT-H-CHASE 4"")"
Range("B12").Select
ActiveCell.FormulaR1C1 = "=COUNTIF(depnames,""JPSPRD EUR-BNK063723516UK 4428"")"
Range("B13").Select
ActiveCell.FormulaR1C1 = "=COUNTIF(depnames,""JPRESF 063726578UK 4"")"
Range("B14").Select
ActiveCell.FormulaR1C1 = "=COUNTIF(depnames,""JPEM EU CAP MK063703207UK 4428"")"
Range("B15").Select
ActiveCell.FormulaR1C1 = "=COUNTIF(depnames,""JPEURP CREDT A063726676UK 4428"")"
Range("B16").Select
ActiveCell.FormulaR1C1 = "=COUNTIF(depnames,""CAZ JV DMC"")"
Range("B17").Select
ActiveCell.FormulaR1C1 = "=COUNTIF(depnames,""Other"")"
Range("B18").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-16]C:R[-1]C)"
Range("C2").Select
ActiveCell.FormulaR1C1 = _
"=SUM(IF(depnames=""PFG CENTRAL EXP"", IF('User Stats'!RC[2]:R[998]C[2]>0,1,0)))"
Range("C3").Select
Selection.FormulaArray = _
"=SUM(IF(depnames=""SECONDARY LOAN TRADING 4"",IF('User Stats'!R[-1]C5:R[997]C5>0,1,0)))"
Range("C4").Select
Selection.FormulaArray = _
"=SUM(IF(depnames=""CONDUITS 4"",IF('User Stats'!R[-2]C5:R[996]C5>0,1,0)))"
Range("C5").Select
Selection.FormulaArray = _
"=SUM(IF(depnames=""UK HIGH YIELD CORP FINANCE 4"",IF('User Stats'!R[-3]C5:R[995]C5>0,1,0)))+SUM(IF(depnames=""FS 4"",IF('User Stats'!R[-3]C5:R[995]C5>0,1,0)))"
Range("C6").Select
Selection.FormulaArray = _
"=SUM(IF(depnames=""FIGRE 44289"",IF('User Stats'!R[-4]C5:R[994]C5>0,1,0)))"
Range("C7").Select
Selection.FormulaArray = _
"=SUM(IF(depnames=""GENERAL INDUSTRIES 4"",IF('User Stats'!R[-5]C5:R[993]C5>0,1,0)))"
Range("C8").Select
Selection.FormulaArray = _
"=SUM(IF(depnames=""DCM CO LOAN CAPITAL MARKETS 44"",IF('User Stats'!R[-6]C5:R[992]C5>0,1,0)))+SUM(IF(depnames=""DCM CO LOAN DISTR DESK 4"",IF('User Stats'!R[-6]C5:R[992]C5>0,1,0)))"
Range("C9").Select
Selection.FormulaArray = _
"=SUM(IF(depnames=""LONDON CAPITAL MARKETS 44289"",IF('User Stats'!R[-7]C5:R[991]C5>0,1,0)))"
Range("C10").Select
Selection.FormulaArray = _
"=SUM(IF(depnames=""RATINGS ADVISORY 44289"",IF('User Stats'!R[-8]C5:R[990]C5>0,1,0)))"
Range("C11").Select
Selection.FormulaArray = _
"=SUM(IF(depnames=""EMEA DCM MGMT-H-CHASE 4"",IF('User Stats'!R[-9]C5:R[989]C5>0,1,0)))"
Range("C11").Select
Selection.FormulaArray = _
"=SUM(IF(depnames=""EMEA DCM MGMT-H-CHASE 4"",IF('User Stats'!R[-9]C5:R[989]C5>0,1,0)))"
Range("C12").Select
Selection.FormulaArray = _
"=SUM(IF(depnames=""JPSPRD EUR-BNK063723516UK 4428"",IF('User Stats'!R[-10]C5:R[988]C5>0,1,0)))"
Range("C13").Select
Selection.FormulaArray = _
"=SUM(IF(depnames=""JPRESF 063726578UK 4"",IF('User Stats'!R[-11]C5:R[987]C5>0,1,0)))"
Range("C14").Select
Selection.FormulaArray = _
"=SUM(IF(depnames=""JPEM EU CAP MK063703207UK 4428"",IF('User Stats'!R[-12]C5:R[986]C5>0,1,0)))"
Range("C15").Select
ActiveCell.FormulaR1C1 = _
"=SUM(IF(depnames=""JPEURP CREDT A063726676UK 4428"",IF('User Stats'!R[-13]C5:R[985]C5>0,1,0)))"
Range("C16").Select
Selection.FormulaArray = _
"=SUM(IF(depnames=""CAZ JV DMC"",IF('User Stats'!R[-14]C5:R[984]C5>0,1,0)))"
Range("C17").Select
Selection.FormulaArray = _
"=SUM(IF(depnames=""Other"",IF('User Stats'!R[-15]C5:R[983]C5>0,1,0)))"
Range("C18").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-16]C:R[-1]C)"
Range("D2").Select
ActiveCell.FormulaR1C1 = "=RC[-1]/RC[-2]"
Range("D3").Select
ActiveCell.FormulaR1C1 = "=RC[-1]/RC[-2]"
Range("D4").Select
ActiveCell.FormulaR1C1 = "=RC[-1]/RC[-2]"
Range("D5").Select
ActiveCell.FormulaR1C1 = "=RC[-1]/RC[-2]"
Range("D6").Select
ActiveCell.FormulaR1C1 = "=RC[-1]/RC[-2]"
Range("D7").Select
ActiveCell.FormulaR1C1 = "=RC[-1]/RC[-2]"
Range("D8").Select
ActiveCell.FormulaR1C1 = "=RC[-1]/RC[-2]"
Range("D9").Select
ActiveCell.FormulaR1C1 = "=RC[-1]/RC[-2]"
Range("D10").Select
ActiveCell.FormulaR1C1 = "=RC[-1]/RC[-2]"
Range("D11").Select
ActiveCell.FormulaR1C1 = "=RC[-1]/RC[-2]"
Range("D12").Select
ActiveCell.FormulaR1C1 = "=RC[-1]/RC[-2]"
Range("D13").Select
ActiveCell.FormulaR1C1 = "=RC[-1]/RC[-2]"
Range("D14").Select
ActiveCell.FormulaR1C1 = "=RC[-1]/RC[-2]"
Range("D15").Select
ActiveCell.FormulaR1C1 = "=RC[-1]/RC[-2]"
Range("D16").Select
ActiveCell.FormulaR1C1 = "=RC[-1]/RC[-2]"
Range("D17").Select
ActiveCell.FormulaR1C1 = "=RC[-1]/RC[-2]"
Range("D18").Select
ActiveCell.FormulaR1C1 = "=RC[-1]/RC[-2]"
Range("E2").Select
Selection.FormulaArray = _
"=SUM(IF(depnames=""PFG CENTRAL EXP"", IF('User Stats'!RC[1]:R[998]C[1]>0,1,0)))"
Range("E3").Select
Selection.FormulaArray = _
"=SUM(IF(depnames=""SECONDARY LOAN TRADING 4"",IF('User Stats'!R[-1]C6:R[997]C6>0,1,0)))"
Range("E4").Select
Selection.FormulaArray = _
"=SUM(IF(depnames=""CONDUITS 4"",IF('User Stats'!R[-2]C6:R[996]C6>0,1,0)))"
Range("E5").Select
Selection.FormulaArray = _
"=SUM(IF(depnames=""UK HIGH YIELD CORP FINANCE 4"",IF('User Stats'!R[-3]C6:R[995]C6>0,1,0)))+SUM(IF(depnames=""FS 4"",IF('User Stats'!R[-3]C6:R[995]C6>0,1,0)))"
Range("E6").Select
Selection.FormulaArray = _
"=SUM(IF(depnames=""FIGRE 44289"",IF('User Stats'!R[-4]C6:R[994]C6>0,1,0)))"
Range("E7").Select
Selection.FormulaArray = _
"=SUM(IF(depnames=""GENERAL INDUSTRIES 4"",IF('User Stats'!R[-5]C6:R[993]C6>0,1,0)))"
Range("E8").Select
Selection.FormulaArray = _
"=SUM(IF(depnames=""DCM CO LOAN CAPITAL MARKETS 44"",IF('User Stats'!R[-6]C6:R[992]C6>0,1,0)))+SUM(IF(depnames=""DCM CO LOAN DISTR DESK 4"",IF('User Stats'!R[-6]C6:R[992]C6>0,1,0)))"
Range("E9").Select
Selection.FormulaArray = _
"=SUM(IF(depnames=""LONDON CAPITAL MARKETS 44289"",IF('User Stats'!R[-7]C6:R[991]C6>0,1,0)))"
Range("E10").Select
Selection.FormulaArray = _
"=SUM(IF(depnames=""RATINGS ADVISORY 44289"",IF('User Stats'!R[-8]C6:R[990]C6>0,1,0)))"
Range("E11").Select
Selection.FormulaArray = _
"=SUM(IF(depnames=""EMEA DCM MGMT-H-CHASE 4"",IF('User Stats'!R[-9]C6:R[989]C6>0,1,0)))"
Range("E12").Select
Selection.FormulaArray = _
"=SUM(IF(depnames=""JPSPRD EUR-BNK063723516UK 4428"",IF('User Stats'!R[-10]C6:R[988]C6>0,1,0)))"
Range("E13").Select
Selection.FormulaArray = _
"=SUM(IF(depnames=""JPRESF 063726578UK 4"",IF('User Stats'!R[-11]C6:R[987]C6>0,1,0)))"
Range("E14").Select
Selection.FormulaArray = _
"=SUM(IF(depnames=""JPEM EU CAP MK063703207UK 4428"",IF('User Stats'!R[-12]C6:R[986]C6>0,1,0)))"
Range("E15").Select
Selection.FormulaArray = _
"=SUM(IF(depnames=""JPEURP CREDT A063726676UK 4428"",IF('User Stats'!R[-13]C6:R[985]C6>0,1,0)))"
Range("E16").Select
Selection.FormulaArray = _
"=SUM(IF(depnames=""CAZ JV DMC"",IF('User Stats'!R[-14]C6:R[984]C6>0,1,0)))"
Range("E17").Select
Selection.FormulaArray = _
"=SUM(IF(depnames=""Other"",IF('User Stats'!R[-15]C6:R[983]C6>0,1,0)))"
Range("E18").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-16]C:R[-1]C)"
Range("F2").Select
ActiveCell.FormulaR1C1 = "=RC[-1]/RC[-4]"
Range("F3").Select
ActiveCell.FormulaR1C1 = "=RC[-1]/RC[-4]"
Range("F4").Select
ActiveCell.FormulaR1C1 = "=RC[-1]/RC[-4]"
Range("F5").Select
ActiveCell.FormulaR1C1 = "=RC[-1]/RC[-4]"
Range("F6").Select
ActiveCell.FormulaR1C1 = "=RC[-1]/RC[-4]"
Range("F7").Select
ActiveCell.FormulaR1C1 = "=RC[-1]/RC[-4]"
Range("F8").Select
ActiveCell.FormulaR1C1 = "=RC[-1]/RC[-4]"
Range("F9").Select
ActiveCell.FormulaR1C1 = "=RC[-1]/RC[-4]"
Range("F10").Select
ActiveCell.FormulaR1C1 = "=RC[-1]/RC[-4]"
Range("F11").Select
ActiveCell.FormulaR1C1 = "=RC[-1]/RC[-4]"
Range("F12").Select
ActiveCell.FormulaR1C1 = "=RC[-1]/RC[-4]"
Range("F13").Select
ActiveCell.FormulaR1C1 = "=RC[-1]/RC[-4]"
Range("F14").Select
ActiveCell.FormulaR1C1 = "=RC[-1]/RC[-4]"
Range("F15").Select
ActiveCell.FormulaR1C1 = "=RC[-1]/RC[-4]"
Range("F16").Select
ActiveCell.FormulaR1C1 = "=RC[-1]/RC[-4]"
Range("F17").Select
ActiveCell.FormulaR1C1 = "=RC[-1]/RC[-4]"
Range("F18").Select
ActiveCell.FormulaR1C1 = "=RC[-1]/RC[-4]"
Range("B2:F17").Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlInsideVertical)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
Range("F20").Select
End Sub
