- ©«¤l
- 438
- ¥DÃD
- 67
- ºëµØ
- 0
- ¿n¤À
- 531
- ÂI¦W
- 30
- §@·~¨t²Î
- win7
- ³nÅ骩¥»
- office 2010
- ¾\ŪÅv
- 50
- ©Ê§O
- ¨k
- µù¥U®É¶¡
- 2012-10-30
- ³Ì«áµn¿ý
- 2024-10-24
|
- Sub SCHPN()
- Dim I As Double
- Dim cnt As Double
- Dim customer As String
- Dim A As String
- Dim LastRec As Integer
- Dim k As Integer
- Dim j As Integer
- Dim l As Integer
- Dim m As Double
- Dim z As Integer
- Dim y As Integer
- Dim e As Integer
- Dim d As Integer
- Dim f As Integer
- Dim FRng As Range
- Dim s1 As String, s2 As String, s3 As String
- Dim wrdApp As Object
-
- j = Worksheets("Oracle").Range("A" & Worksheets("Oracle").Rows.Count).End(xlUp).Row
- l = Worksheets("Follower").Range("A" & Worksheets("Follower").Rows.Count).End(xlUp).Row
- z = Worksheets("Rule").Range("B" & Worksheets("Rule").Rows.Count).End(xlUp).Row
- y = 39
- Do
- Set wrdApp = CreateObject("Word.Document")
-
- Application.ScreenUpdating = False
- f = 0
- d = 0
- e = 0
- cnt = 74
-
- A = Worksheets("Rule").Cells(y, 2).Value
- customer = A
- Worksheets("SchPN").Copy After:=Worksheets(Worksheets.Count)
- Worksheets(Worksheets.Count).Name = customer
- Worksheets(customer).Range("H5").Value = Date
- For I = 2 To j
- If Worksheets("Oracle").Cells(I, 5).Value = customer And Worksheets("Oracle").Cells(I, 19).Value > 0 And Trim(Worksheets("Oracle").Cells(I, 19).Value) <> "" And Trim(Worksheets("Oracle").Cells(I, 15).Value) = "" And (Trim(Worksheets("Oracle").Cells(I, 20).Value) = Trim(Worksheets("Oracle").Cells(I, 18).Value) Or Trim(Worksheets("Oracle").Cells(I, 20).Value) = "") Then
- If Left((Worksheets("Oracle").Cells(I, 22).Value), 1) = 1 Or Left((Worksheets("Oracle").Cells(I, 22).Value), 1) = 2 Or Left((Worksheets("Oracle").Cells(I, 22).Value), 1) = 3 Or Left((Worksheets("Oracle").Cells(I, 22).Value), 1) = 4 Or Left((Worksheets("Oracle").Cells(I, 22).Value), 1) = 5 Or Left((Worksheets("Oracle").Cells(I, 22).Value), 1) = 6 Or Left((Worksheets("Oracle").Cells(I, 22).Value), 1) = 7 Or Left((Worksheets("Oracle").Cells(I, 22).Value), 1) = 8 Or Left((Worksheets("Oracle").Cells(I, 22).Value), 1) = 9 Then
- Set FRng = Worksheets(customer).Range("A:A").Find(Worksheets("Oracle").Cells(I, 1).Value, lookat:=xlWhole, SearchDirection:=xlPrevious)
- If InStr(UCase(Worksheets("Oracle").Cells(I, 2).MergeArea(1)), "SPOT") = 0 And InStr(UCase(Worksheets("Oracle").Cells(I, 2).MergeArea(1)), "³q内´µ现货") = 0 Then
- If FRng Is Nothing Then
- Worksheets(customer).Cells(cnt, 1).Value = Worksheets("Oracle").Cells(I, 1).Value
- Worksheets(customer).Cells(cnt, 2).Value = Worksheets("Oracle").Cells(I, 24).Value
- Worksheets(customer).Cells(cnt, 3).Value = Worksheets("Oracle").Cells(I, 14).Value
- If IsError(Application.VLookup(Worksheets(customer).Cells(cnt, 1).Value, Sheets("State").Range("A:B"), 2, False)) Then Worksheets(customer).Cells(cnt, 8).Value = "«Ý³qª¾" Else If Trim(Application.VLookup(Worksheets(customer).Cells(cnt, 1).Value, Sheets("State").Range("A:B"), 2, False)) = "" Then Worksheets(customer).Cells(cnt, 8).Value = "«Ý³qª¾" Else Worksheets(customer).Cells(cnt, 8).Value = Application.VLookup(Worksheets(customer).Cells(cnt, 1).Value, Sheets("State").Range("A:B"), 2, False)
- Worksheets(customer).Cells(cnt, 19).Value = Left((Worksheets("Oracle").Cells(I, 22).Value), 3)
- Worksheets(customer).Cells(cnt, 9).Value = "USD" & Format(Worksheets("Oracle").Cells(I, 19).Value * Worksheets(customer).Cells(cnt, 19).Value, "#.00")
- Worksheets(customer).Cells(cnt, 19).Value = ""
-
- If Left((Worksheets("Oracle").Cells(I, 1).Value), 1) = 8 Then
- s1 = Left((Worksheets("Oracle").Cells(I, 22).Value), 3) & "©wª÷"
- s2 = " ¥N²z:" & Worksheets("Oracle").Cells(I, 10).Value
- s3 = " ¬ü°ê "
- S = s1 & " ¦X¦Pª÷ÃB¡GUSD" & Worksheets("Oracle").Cells(I, 19).Value & " ¥Øªº´ä¡G" & Worksheets("Oracle").Cells(I, 26).Value & s2 & s3
- With Worksheets(customer).Cells(cnt, 10)
- .Value = S
- .Characters(InStr(S, s1), Len(s1)).Font.Color = vbRed
- .Characters(InStr(S, s2), Len(s2)).Font.Color = -16777024
- .Characters(InStr(S, s3), Len(s3)).Font.Color = vbBlue
- .Value = T_S_Cvt(.Value, 1)
- End With
- End If
-
- If Left((Worksheets("Oracle").Cells(I, 1).Value), 1) = 2 Then
- s1 = Left((Worksheets("Oracle").Cells(I, 22).Value), 3) & "©wª÷"
- s2 = " ¥N²z:" & Worksheets("Oracle").Cells(I, 10).Value
- s3 = " ·ç¤h"
- S = s1 & " ¦X¦Pª÷ÃB¡GUSD" & Worksheets("Oracle").Cells(I, 19).Value & " ¥Øªº´ä¡G" & Worksheets("Oracle").Cells(I, 26).Value & s2 & s3
- With Worksheets(customer).Cells(cnt, 10)
- .Value = S
- .Characters(InStr(S, s1), Len(s1)).Font.Color = vbRed
- .Characters(InStr(S, s2), Len(s2)).Font.Color = -16777024
- .Characters(InStr(S, s3), Len(s3)).Font.Color = vbBlue
- .Value = T_S_Cvt(.Value, 1)
- End With
- End If
-
- Worksheets(customer).Cells(cnt, 12).Value = Worksheets("Oracle").Cells(I, 19).Value
- Worksheets(customer).Cells(cnt, 15).Value = Worksheets("Oracle").Cells(I, 22).Value
- Worksheets(customer).Cells(cnt, 11).Value = Worksheets("Oracle").Cells(I, 15).Value
- If IsError(Application.VLookup(Worksheets(customer).Cells(cnt, 1).Value, Sheets("State").Range("A:B"), 2, False)) Then Worksheets(customer).Cells(cnt, 13).Value = Worksheets("Oracle").Cells(I, 28).Value Else Worksheets(customer).Cells(cnt, 13).Value = Application.VLookup(Worksheets(customer).Cells(cnt, 1).Value, Sheets("State").Range("A:C"), 2, False)
- Worksheets(customer).Cells(cnt, 14).Value = Worksheets("Oracle").Cells(I, 27).Value
- If IsError(Application.VLookup(Worksheets(customer).Cells(cnt, 1).Value, Sheets("State").Range("A:B"), 2, False)) Then Worksheets(customer).Cells(cnt, 16).Value = "" Else Worksheets(customer).Cells(cnt, 16).Value = Application.VLookup(Worksheets(customer).Cells(cnt, 1).Value, Sheets("State").Range("A:Q"), 17, False)
- If IsError(Application.VLookup(Worksheets(customer).Cells(cnt, 1).Value, Sheets("State").Range("A:B"), 2, False)) Then Worksheets(customer).Cells(cnt, 17).Value = "" Else Worksheets(customer).Cells(cnt, 17).Value = Application.VLookup(Worksheets(customer).Cells(cnt, 1).Value, Sheets("State").Range("A:E"), 5, False)
- If IsError(Application.VLookup(Worksheets(customer).Cells(cnt, 1).Value, Sheets("State").Range("A:B"), 2, False)) Then Worksheets(customer).Cells(cnt, 18).Value = Worksheets("Oracle").Cells(I, 25).Value Else Worksheets(customer).Cells(cnt, 18).Value = Application.VLookup(Worksheets(customer).Cells(cnt, 1).Value, Sheets("State").Range("A:X"), 24, False)
- Worksheets(customer).Cells(cnt, 19).Value = Worksheets("Oracle").Cells(I, 18).Value
- If Trim(Worksheets("Oracle").Cells(I, 8).Value) = "" Then Worksheets(customer).Cells(cnt, 5).Value = "¨S¦³" Else Worksheets(customer).Cells(cnt, 5).Value = "¦³"
- If Worksheets(customer).Cells(cnt, 5).Value = "¨S¦³" Then Worksheets(customer).Cells(cnt, 5).Font.Color = vbRed
- Worksheets(customer).Cells(cnt, 4).Value = Worksheets("Oracle").Cells(I, 7).Value
- Worksheets(customer).Cells(cnt, 6).Value = Worksheets("Oracle").Cells(I, 12).Value
- If Trim(Worksheets("Oracle").Cells(I, 27).Value) = "" Then Worksheets(customer).Cells(cnt, 7).Value = "«Ý³qª¾" Else Worksheets(customer).Cells(cnt, 7).Value = Worksheets("Oracle").Cells(I, 27).Value
- With Worksheets(customer).Range(Worksheets(customer).Cells(cnt, 1), Worksheets(customer).Cells(cnt, 10))
-
- .Borders.LineStyle = 1
-
- .Borders.LineStyle = 1
-
- .Borders.ColorIndex = 0
-
- .BorderAround , 2, 0
-
- End With
- cnt = cnt + 1
- d = d + 1
- e = e + 1
- End If
- End If
- End If
- End If
- Next I
- Loop Until y > z
- Application.ScreenUpdating = True
-
- wrdApp.Close False
- End Sub
- Public Function T_S_Cvt(strData, bytOption) As String
-
- With wrdApp
-
- .Content = strData
-
- ' ½Õ¥Î Word TCSCConverter ¤èªk¨ÓÂà´«Ác²Åé
-
- .Range.TCSCConverter bytOption, True, True
-
- T_S_Cvt = .Content
-
- End With
-
- End Function
½Æ»s¥N½X ¦^´_ 3# c_c_lai
.content = strData ¥X²{RUN-TIME ERROR'424': Object required |
|