- ©«¤l
- 430
- ¥DÃD
- 65
- ºëµØ
- 0
- ¿n¤À
- 521
- ÂI¦W
- 30
- §@·~¨t²Î
- win7
- ³nÅ骩¥»
- office 2010
- ¾\ŪÅv
- 50
- ©Ê§O
- ¨k
- µù¥U®É¶¡
- 2012-10-30
- ³Ì«áµn¿ý
- 2024-8-13
|
- Sub Schedule()
- Dim I As Double
- Dim cnt As Double
- Dim customer As String
- Dim z As String
- Dim a As Integer
- Dim b As Integer
- Dim c As Integer
- Dim d As Integer
- Dim e As Integer
- Dim f As Integer
- Dim m As Double
- Dim FRng As Range
- Dim Rng As Range
- a = Worksheets("Oracle").Range("A" & Worksheets("Oracle").Rows.Count).End(xlUp).Row
- b = Worksheets("Client Detial").Range("A" & Worksheets("Client Detial").Rows.Count).End(xlUp).Row
- c = Worksheets("Rule").Range("A" & Worksheets("Rule").Rows.Count).End(xlUp).Row
- d = 1
- Do
- cnt = 11
-
- z = Worksheets("Rule").Cells(d, 1).Value
- customer = z
- Worksheets("Form").Copy After:=Worksheets(Worksheets.Count)
-
- Worksheets(Worksheets.Count).Name = customer
- Worksheets(customer).Range("J4").Value = Date
- For I = 2 To a
- If Worksheets("Oracle").Cells(I, 5).Value = customer And Worksheets("Oracle").Cells(I, 20).Value <> 0 Then
- Set FRng = Worksheets(customer).Range("B:B").Find(Worksheets("Oracle").Cells(I, 1).Value, lookat:=xlWhole, SearchDirection:=xlPrevious)
- If FRng Is Nothing Then
- Set Rng = Worksheets("Filter").Range("A:A").Find(Worksheets("Oracle").Cells(I, 1), lookat:=xlWhole)
- If Rng Is Nothing Then
- Worksheets(customer).Cells(cnt, 1).Value = Worksheets("Oracle").Cells(I, 7).Value
- Worksheets(customer).Cells(cnt, 2).Value = Worksheets("Oracle").Cells(I, 1).Value
- Worksheets(customer).Cells(cnt, 4).Value = Worksheets("Oracle").Cells(I, 24).Value
- Worksheets(customer).Cells(cnt, 5).Value = Worksheets("Oracle").Cells(I, 14).Value
- Worksheets(customer).Cells(cnt, 6).Value = Worksheets("Oracle").Cells(I, 12).Value
- Worksheets(customer).Cells(cnt, 7).Value = Worksheets("Oracle").Cells(I, 26).Value
- Worksheets(customer).Cells(cnt, 8).Value = Worksheets("Oracle").Cells(I, 41).Value
- Worksheets(customer).Cells(cnt, 9).Value = Split(Worksheets("Oracle").Cells(I, 42).Value, "/")(0)
- Worksheets(customer).Cells(cnt, 10).Value = Worksheets("Oracle").Cells(I, 27).Value
- Worksheets(customer).Cells(cnt, 11).Value = Worksheets("Oracle").Cells(I, 28).Value
- Worksheets(customer).Cells(cnt, 13).Value = Worksheets("Oracle").Cells(I, 20).Value
- Worksheets(customer).Cells(cnt, 14).Value = Worksheets("Oracle").Cells(I, 18).Value
- Worksheets(customer).Cells(cnt, 15).Value = Worksheets("Oracle").Cells(I, 2).Value
- Worksheets(customer).Cells(cnt, 16).Value = Worksheets("Oracle").Cells(I, 60).Value
- If Trim(Worksheets("Oracle").Cells(I, 8).Value) = "" Then
- Worksheets(customer).Cells(cnt, 3).Value = "NO"
- Else
- Worksheets(customer).Cells(cnt, 3).Value ="YES"
- End If
- cnt = cnt + 1
- End If
- End If
- End If
-
- If Worksheets("Oracle").Cells(I, 5).Value = customer And Trim(Worksheets("Oracle").Cells(I, 20).Value) = "" Then
- Set FRng = Worksheets(customer).Range("B:B").Find(Worksheets("Oracle").Cells(I, 1).Value, lookat:=xlWhole, SearchDirection:=xlPrevious)
- If FRng Is Nothing Then
- Set Rng = Worksheets("Filter").Range("A:A").Find(Worksheets("Oracle").Cells(I, 1), lookat:=xlWhole)
- If Rng Is Nothing Then
- Worksheets(customer).Cells(cnt, 1).Value = Worksheets("Oracle").Cells(I, 7).Value
- Worksheets(customer).Cells(cnt, 2).Value = Worksheets("Oracle").Cells(I, 1).Value
- Worksheets(customer).Cells(cnt, 4).Value = Worksheets("Oracle").Cells(I, 24).Value
- Worksheets(customer).Cells(cnt, 5).Value = Worksheets("Oracle").Cells(I, 14).Value
- Worksheets(customer).Cells(cnt, 6).Value = Worksheets("Oracle").Cells(I, 12).Value
- Worksheets(customer).Cells(cnt, 7).Value = Worksheets("Oracle").Cells(I, 26).Value
- Worksheets(customer).Cells(cnt, 8).Value = Worksheets("Oracle").Cells(I, 41).Value
- Worksheets(customer).Cells(cnt, 10).Value = Worksheets("Oracle").Cells(I, 27).Value
- Worksheets(customer).Cells(cnt, 11).Value = Worksheets("Oracle").Cells(I, 28).Value
- Worksheets(customer).Cells(cnt, 13).Value = Worksheets("Oracle").Cells(I, 20).Value
- Worksheets(customer).Cells(cnt, 14).Value = Worksheets("Oracle").Cells(I, 18).Value
- Worksheets(customer).Cells(cnt, 15).Value = Worksheets("Oracle").Cells(I, 2).Value
- Worksheets(customer).Cells(cnt, 16).Value = Worksheets("Oracle").Cells(I, 60).Value
- If Trim(Worksheets("Oracle").Cells(I, 8).Value) = "" Then
- Worksheets(customer).Cells(cnt, 3).Value = "NO"
- Else
- Worksheets(customer).Cells(cnt, 3).Value = "YES"
- End If
- If Trim(Worksheets("Oracle").Cells(I, 42).Value) = "" Then
- Worksheets(customer).Cells(cnt, 9).Value = ""
- Else
- Worksheets(customer).Cells(cnt, 9).Value = Split(Worksheets("Oracle").Cells(I, 42).Value, "/")(0)
- End If
- cnt = cnt + 1
- End If
- End If
- End If
-
- If Worksheets("Oracle").Cells(I, 5).Value = customer And Worksheets("Oracle").Cells(I, 20).Value = 0 And Worksheets("Oracle").Cells(I, 28).Value >= Date Then
- Set FRng = Worksheets(customer).Range("B:B").Find(Worksheets("Oracle").Cells(I, 1).Value, lookat:=xlWhole, SearchDirection:=xlPrevious)
- If FRng Is Nothing Then
- Set Rng = Worksheets("Filter").Range("A:A").Find(Worksheets("Oracle").Cells(I, 1), lookat:=xlWhole)
- If Rng Is Nothing Then
- Worksheets(customer).Cells(cnt, 1).Value = Worksheets("Oracle").Cells(I, 7).Value
- Worksheets(customer).Cells(cnt, 2).Value = Worksheets("Oracle").Cells(I, 1).Value
- Worksheets(customer).Cells(cnt, 4).Value = Worksheets("Oracle").Cells(I, 24).Value
- Worksheets(customer).Cells(cnt, 5).Value = Worksheets("Oracle").Cells(I, 14).Value
- Worksheets(customer).Cells(cnt, 6).Value = Worksheets("Oracle").Cells(I, 12).Value
- Worksheets(customer).Cells(cnt, 7).Value = Worksheets("Oracle").Cells(I, 26).Value
- Worksheets(customer).Cells(cnt, 8).Value = Worksheets("Oracle").Cells(I, 41).Value
- Worksheets(customer).Cells(cnt, 9).Value = Split(Worksheets("Oracle").Cells(I, 42).Value, "/")(0)
- Worksheets(customer).Cells(cnt, 10).Value = Worksheets("Oracle").Cells(I, 27).Value
- Worksheets(customer).Cells(cnt, 11).Value = Worksheets("Oracle").Cells(I, 28).Value
- Worksheets(customer).Cells(cnt, 13).Value = Worksheets("Oracle").Cells(I, 20).Value
- Worksheets(customer).Cells(cnt, 14).Value = Worksheets("Oracle").Cells(I, 18).Value
- Worksheets(customer).Cells(cnt, 15).Value = Worksheets("Oracle").Cells(I, 2).Value
- Worksheets(customer).Cells(cnt, 16).Value = Worksheets("Oracle").Cells(I, 60).Value
- If Trim(Worksheets("Oracle").Cells(I, 18).Value) <> "" Then Worksheets(customer).Cells(cnt, 12).Value = "ÒѸ¶¿î"
- If Trim(Worksheets("Oracle").Cells(I, 8).Value) = "" Then
- Worksheets(customer).Cells(cnt, 3).Value = "NO"
- Else
- Worksheets(customer).Cells(cnt, 3).Value = "YES"
- End If
- cnt = cnt + 1
- End If
- End If
- End If
- Next I
-
- For I = 2 To b
- If Worksheets("Client Detial").Cells(I, 1).Value = customer Then
- Worksheets(customer).Cells(5, 3).Value = Worksheets("Client Detial").Cells(I, 4).Value & " - " & Worksheets("Client Detial").Cells(I, 5).Value
- Worksheets(customer).Cells(6, 3).Value = Worksheets("Client Detial").Cells(I, 8).Value
- End If
- Next I
-
- e = Worksheets(customer).Range("A" & Worksheets(customer).Rows.Count).End(xlUp).Row
-
- With Worksheets(customer).Range(Cells(11, 1), Cells(e, 12))
- .Borders.LineStyle = 1
-
- .Borders.LineStyle = 1
-
- .Borders.ColorIndex = 0
-
- .BorderAround , 2, 0
- End With
-
- d = d + 1
- Loop Until d > c
- End Sub
½Æ»s¥N½X ¦^´_ 38# GBKEE
¦pªG§Ú¦X¦b¤@°_¹B¦æ´N·|¥X²{¨º°ÝÃD¡A¦ý¬O¦pªG§Ú±N¤U±³o¬q¤À¶}¥t¥~¤@Ó¿W¥ßªºµ{¦¡¡A´N¨S¦³°ÝÃD
e = Worksheets(customer).Range("A" & Worksheets(customer).Rows.Count).End(xlUp).Row
With Worksheets(customer).Range(Cells(11, 1), Cells(e, 12))
.Borders.LineStyle = 1
.Borders.LineStyle = 1
.Borders.ColorIndex = 0
.BorderAround , 2, 0
End With |
|