Sub Update() - Tech Support Guy

towerdevelopmentΔιαχείριση Δεδομένων

16 Δεκ 2012 (πριν από 4 χρόνια και 6 μήνες)

328 εμφανίσεις

Sub Update()


Dim BMW As Byte


Dim BUICK As Byte


Dim CHRYSLER As Byte


Dim EOSA As Byte


Dim FORD As Byte


Dim MACK As Byte


Dim HARLEY As Byte


Dim ISUZU As Byte


Dim MMMA As Byte


Dim NISSAN As Byte


Dim SUBARU As Byte



Dim TOYOTA As Byte


Dim HONDA As Byte


Dim HORN As Byte


Dim VOID As Byte


Dim DM As Byte


Dim PLD As Byte


Dim CCMonth As Single


Dim DMMonth As Single


Dim SortMonth As Single


Dim Today1 As Variant


Dim X As Single


D
im Y As Single


Dim OP As Integer


Dim INTERNAL As Integer


Dim EXTERNAL As Integer


Dim CONCERN As Integer


Dim COMPLAINT As Integer


Dim D As Integer






Sheets("DM REPORT").Select


Today1 = Range("A2")


Rows("26:100
0").Select


Selection.Delete




X = 21


Sheets("SORT REPORT").Select


Range("D21").Select


Do Until ActiveCell = ""


If ActiveCell = Today1 Then


ActiveCell.Offset(0,
-
1).Select


If ActiveCell = "INTERNAL" The
n


INTERNAL = INTERNAL + 1


ElseIf ActiveCell = "EXTERNAL" Then


EXTERNAL = EXTERNAL + 1


End If


ActiveCell.Offset(1, 1).Select


Else


ActiveCell.Offset(1, 0).Select


End If


X = X + 1


Loop


Range("A1").Select






Sheets("CC REPORT").Select


X = 8


Range("A8").Select


Do Until ActiveCell = ""


If ActiveCell = Today1 Then


ActiveCell.Offset(0, 2).Select


If Ac
tiveCell = "CONCERN" Then


CONCERN = CONCERN + 1


ElseIf ActiveCell = "COMPLAINT" Then


COMPLAINT = COMPLAINT + 1


End If


ActiveCell.Offset(1,
-
2).Select


Else


ActiveCell.Of
fset(1, 0).Select


End If


X = X + 1


Loop








Sheets("DM'S").Select


Range("D3").Select


X = 3


Y = 26




Do Until ActiveCell = ""


If ActiveCell = Today1 Then


If ActiveCell.Offset(0,
-
2) = ActiveCell.Offset(
-
1,
-
2) Then


ActiveCell.Offset(1, 0).Select


GoTo 100


Else: ActiveCell.Offset(0, 10).Select


If ActiveCell <> "VOID" Then DM = DM + 1


If ActiveCell = "VOID" Then



VOID = VOID + 1


ElseIf ActiveCell = "BMW" Then


BMW = BMW + 1


ElseIf ActiveCell = "BUICK" Then


BUICK = BUICK + 1


ElseIf ActiveCell = "CHRYSLER" Then


CHRYSLER = C
HRYSLER + 1


ElseIf ActiveCell = "EOSA" Then


EOSA = EOSA + 1


ElseIf ActiveCell = "FORD" Then


FORD = FORD + 1


ElseIf ActiveCell = "MACK" Then


MACK = MACK + 1


Else
If ActiveCell = "HARLEY" Then


HARLEY = HARLEY + 1


ElseIf ActiveCell = "ISUZU" Then


ISUZU = ISUZU + 1


ElseIf ActiveCell = "MMMA" Then


MMMA = MMMA + 1


ElseIf ActiveCell = "NI
SSAN" Then


NISSAN = NISSAN + 1


ElseIf ActiveCell = "SUBARU" Then


SUBARU = SUBARU + 1


ElseIf ActiveCell = "TOYOTA" Then


TOYOTA = TOYOTA + 1


ElseIf ActiveCell = "HONDA" Then


HONDA = HONDA + 1


ElseIf ActiveCell = "HORN" Then


HORN = HORN + 1


End If

100 Rows(X).Select


Selection.Copy


Sheets("DM REPORT").Select


Rows(Y).Select



ActiveSheet.Paste


Y = Y + 1


Sheets("DM'S").Select


ActiveCell.Offset(1, 3).Select


End If


Else


ActiveCell.Offset(1, 0).Select


End If


X = X + 1


Loop




' SortDat
a





Sheets("CHART DATA").Select


Range("L36:L50").Select


Selection.ClearContents


Range("K36").Select


Do Until ActiveCell = "TOTAL"


If ActiveCell = "BMW" Then


If BMW <> 0 Then


ActiveCell.Offset(0, 1
).Select


ActiveCell = BMW


ActiveCell.Offset(0,
-
1).Select


End If


ElseIf ActiveCell = "BUICK" Then


If BUICK <> 0 Then


ActiveCell.Offset(0, 1).Select


ActiveCell =

BUICK


ActiveCell.Offset(0,
-
1).Select


End If


ElseIf ActiveCell = "CHRYSLER" Then


If CHRYSLER <> 0 Then


ActiveCell.Offset(0, 1).Select


ActiveCell = CHRYSLER


Act
iveCell.Offset(0,
-
1).Select


End If


ElseIf ActiveCell = "EOSA" Then


If EOSA <> 0 Then


ActiveCell.Offset(0, 1).Select


ActiveCell = EOSA


ActiveCell.Offset(0,
-
1).Select



End If


ElseIf ActiveCell = "FORD" Then


If FORD <> 0 Then


ActiveCell.Offset(0, 1).Select


ActiveCell = FORD


ActiveCell.Offset(0,
-
1).Select


End If


ElseIf ActiveCell =
"MACK" Then


If MACK <> 0 Then


ActiveCell.Offset(0, 1).Select


ActiveCell = MACK


ActiveCell.Offset(0,
-
1).Select


End If


ElseIf ActiveCell = "HARLEY" Then


If HARLEY <>

0 Then


ActiveCell.Offset(0, 1).Select


ActiveCell = HARLEY


ActiveCell.Offset(0,
-
1).Select


End If


ElseIf ActiveCell = "ISUZU" Then


If ISUZU <> 0 Then


ActiveCell
.Offset(0, 1).Select


ActiveCell = ISUZU


ActiveCell.Offset(0,
-
1).Select


End If


ElseIf ActiveCell = "MMMA" Then


If MMMA <> 0 Then


ActiveCell.Offset(0, 1).Select


ActiveCell = MMMA


ActiveCell.Offset(0,
-
1).Select


End If


ElseIf ActiveCell = "NISSAN" Then


If NISSAN <> 0 Then


ActiveCell.Offset(0, 1).Select


ActiveCell = NISSAN



ActiveCell.Offset(0,
-
1).Select


End If


ElseIf ActiveCell = "SUBARU" Then


If SUBARU <> 0 Then


ActiveCell.Offset(0, 1).Select


ActiveCell = SUBARU


ActiveCell.Offset(0,
-
1).Selec
t


End If


ElseIf ActiveCell = "TOYOTA" Then


If TOYOTA <> 0 Then


ActiveCell.Offset(0, 1).Select


ActiveCell = TOYOTA


ActiveCell.Offset(0,
-
1).Select


End If


Els
eIf ActiveCell = "HONDA" Then


If HONDA <> 0 Then


ActiveCell.Offset(0, 1).Select


ActiveCell = HONDA


ActiveCell.Offset(0,
-
1).Select


End If


ElseIf ActiveCell = "HORN" Then



If HORN <> 0 Then


ActiveCell.Offset(0, 1).Select


ActiveCell = HORN


ActiveCell.Offset(0,
-
1).Select


End If


End If


ActiveCell.Offset(1, 0).Select


Loop




Range("K36:L
50").Select


Selection.Sort Key1:=Range("L36"), Order1:=xlDescending, Header:= _


xlNo, OrderCustom:=1, MatchCase:=False, Orientation:= _


xlTopToBottom


DM = Range("L51")


X = Range("E3")


D = Range("E4")


Range("B2").Select



For Y = 1 To D


If ActiveCell = "" Then


ActiveCell = 0


ActiveCell.Offset(0, 1).Select


ActiveCell = 0


ActiveCell.Offset(0, 4).Select


ActiveCell = 0


ActiveCell.Offset(0, 1).Sel
ect


ActiveCell = 0


ActiveCell.Offset(0, 4).Select


ActiveCell = 0


ActiveCell.Offset(0, 1).Select


ActiveCell = 0


ActiveCell.Offset(1,
-
11).Select


Else


ActiveCell.Offs
et(1, 0).Select


End If


Next Y


ActiveCell = COMPLAINT


ActiveCell.Offset(0, 1).Select


ActiveCell = CONCERN


ActiveCell.Offset(0, 4).Select


ActiveCell = DM


ActiveCell.Offset(0, 1).Select


ActiveCell = PLD


ActiveCell.O
ffset(0, 4).Select


ActiveCell = EXTERNAL


ActiveCell.Offset(0, 1).Select


ActiveCell = INTERNAL


CCMonth = Range("D33")


DMMonth = Range("I33")


SortMonth = Range("N33")




Range("C36").Select


For Y = 1 To X


If ActiveCel
l = "" Then


ActiveCell = 0


End If


ActiveCell.Offset(1, 0).Select


Next Y


ActiveCell = CCMonth


ActiveCell.Offset(0, 5).Select


ActiveCell = DMMonth


' FitChart



' StartDate = Range("Data!$J$2")


Dim DM_De
partment_Count As Single


DM_Department_Count = Range("M36")


Sheets("DM REPORT").Select


ActiveSheet.ChartObjects("Chart 169").Activate


With ActiveChart.SeriesCollection(1)


.Name = "='CHART DATA'!R36C11:R"
+ Format
(DM_Department_Count

+ 35) + "C11"


.Values = "='CHART DATA'!R36C12:R" + Format(DM_Department_Count + 35) + "C12"


End With


Range("A1").Select

End Sub


Sub UpdateOpenDMs()



Sheets("OPEN DM LOG").Select


Range("A3:Q1000").Select


Selection.Delete


Ra
nge("A3").Select


Sheets("DM'S").Select


Range("A3").Select


X = 3


Do Until ActiveCell = ""


ActiveCell.Offset(0, 4).Select


If ActiveCell = "" Then


ActiveCell.Offset(0,
-
1).Select


If ActiveCell = Today1 T
hen


ActiveCell.Offset(1,
-
3).Select


Else


OP = OP + 1


Rows(X).Select


Selection.Copy


Sheets("OPEN DM LOG").Select


ActiveSheet.Paste


Ac
tiveCell.Offset(1, 0).Select


Sheets("DM'S").Select


ActiveCell.Offset(1, 0).Select


End If


Else


ActiveCell.Offset(1,
-
4).Select


End If


X = X + 1


Loop


Application.CutCopyM
ode = False


Sheets("OPEN DM LOG").Select

End Sub