ªð¦^¦Cªí ¤W¤@¥DÃD µo©«

½ÐÀ°¦£Â½Ä¶¤@¤Uµ{¦¡

½ÐÀ°¦£Â½Ä¶¤@¤Uµ{¦¡

¦³¨â­Óexcel¡A¤À§O¬Oa.xlsx ©Mb.xlsx
§Ú·Q¦ba.xlsx¨Çvba, Ū¨úb.xlsxªº¸ê®Æ¡C
½Ð°Ý§ÚÀ³¸Ó¦p¦ó¼g
for i = b.xlsxªº³Ì«á¤@µ§ to 1
¦]¬°§Ú·Q¥Îb.xlsx³Ì«á¤@Äæ¶}©l©¹¤WÀˬda.xlsx
¦ý¤£ª¾¹D«ç¼Ë¼g¤~¥i¥Hª¾¹Db.xlsxªº³Ì«á¤@µ§

Sub ex()
Dim FRng As Range
Dim A As Range, Rng As Range
  fs = "c:\user\destop\b.xlsx"
'fs = ThisWorkbook.Path & "\b.xlsx"
set wb = Workbooks.Open(fs)
     Set FRng = wb.Sheets("sheet1").Range("k:k").Find(today(), lookat:=xlWhole, SearchDirection:=xlPrevious) ¦bb.xlsx ªºKÄæ´M§äµ¥·í¤éªº¤é´Áªº¤@¦C
     If Not FRng Is Nothing Then °²¦p§ä¨ì
        If FRng.Offset(, -3).Value >= 0.95 Then °²¦pb.xlsx ³o¦CªºHÄ檺­È¤j¹L©Îµ¥©ó0.95
set rng = workbooks(a).range("a:a").find(Frng.offset(,-9), lookat:=xlWhole, SearchDirection:=xlPrevious)     ¦ba.xlsx ªºAÄæ´M§äb.xlsx ³o¦CªºBÄ檺­È¬O§_¦s¦b
If FRng Is Nothing Then  °²¦p§ä¤£¨ì
                ¦ba.xlsx ªºAÄæ³Ì«á¤@¦C¥[¤Wb.xlsx ³o¦CªºBÄ檺­È¡A¤Î   ¦ba.xlsx ªºFÄæ³Ì«á¤@¦C¥[¤Wb.xlsx ³o¦CªºKÄ檺­È

end sub

Desktop.rar (80.19 KB) ¦^´_ 43# GBKEE

TOP

¦^´_ 42# 198188
¤£¦n·N«ä:2003¶}±Ò¦¹2010¬¡­¶Ã¯¦]¦³¤Ó¦h®æ¦¡,¾É­P¤£¯à¥¿±`¶}±Ò¦¹¬¡­¶Ã¯
¥i§_¥t¦s2003ª©¥[¤W°ÝÃDªº»¡©ú¤W¶Ç
·P®¦ªº¤ß......(¦b³Â»¶®a±Ú°Q½×°Ï.¥Î¤ß¾Ç²ß·|¦³¶i¨Bªº)
¦ý¸ê·½µL­­,«á´©¦³­­,  ¤@¤Ñ1¤¸ªºÃÙ§U,¤H¤H¦³¯à¤O.

TOP

¦^´_ 41# GBKEE

·PÁ¤j¤jÀ°¦£¡I
½Ð°Ý¥i§_À°§Ú¬Ý¬Ý¤U­±LINKªº°ÝÃD¡H
    http://forum.twbts.com/viewthrea ... amp;page=2#pid52598

TOP

¦^´_ 40# 198188
¸Õ¸Õ¬Ý
  1. With Worksheets(customer)
  2.     With .Range(.Cells(11, 1), .Cells(e, 12))
  3.         .Borders.LineStyle = 1
  4.         .Borders.LineStyle = 1
  5.         .Borders.ColorIndex = 0
  6.         .BorderAround , 2, 0
  7.     End With
  8. End With
½Æ»s¥N½X
·P®¦ªº¤ß......(¦b³Â»¶®a±Ú°Q½×°Ï.¥Î¤ß¾Ç²ß·|¦³¶i¨Bªº)
¦ý¸ê·½µL­­,«á´©¦³­­,  ¤@¤Ñ1¤¸ªºÃÙ§U,¤H¤H¦³¯à¤O.

TOP

  1. Sub Schedule()
  2.   Dim I As Double
  3.   Dim cnt As Double
  4.   Dim customer As String
  5.   Dim z As String
  6.   Dim a As Integer
  7.   Dim b As Integer
  8.   Dim c As Integer
  9.   Dim d As Integer
  10.   Dim e As Integer
  11.   Dim f As Integer
  12.   Dim m As Double
  13.   Dim FRng As Range
  14.   Dim Rng As Range
  15.   a = Worksheets("Oracle").Range("A" & Worksheets("Oracle").Rows.Count).End(xlUp).Row
  16.   b = Worksheets("Client Detial").Range("A" & Worksheets("Client Detial").Rows.Count).End(xlUp).Row
  17.   c = Worksheets("Rule").Range("A" & Worksheets("Rule").Rows.Count).End(xlUp).Row
  18. d = 1
  19. Do
  20.   cnt = 11
  21.    
  22.   z = Worksheets("Rule").Cells(d, 1).Value
  23.   customer = z
  24.   Worksheets("Form").Copy After:=Worksheets(Worksheets.Count)
  25.   
  26.   Worksheets(Worksheets.Count).Name = customer
  27.   Worksheets(customer).Range("J4").Value = Date

  28. For I = 2 To a
  29.   If Worksheets("Oracle").Cells(I, 5).Value = customer And Worksheets("Oracle").Cells(I, 20).Value <> 0 Then
  30.   Set FRng = Worksheets(customer).Range("B:B").Find(Worksheets("Oracle").Cells(I, 1).Value, lookat:=xlWhole, SearchDirection:=xlPrevious)
  31.   If FRng Is Nothing Then
  32.   Set Rng = Worksheets("Filter").Range("A:A").Find(Worksheets("Oracle").Cells(I, 1), lookat:=xlWhole)
  33.   If Rng Is Nothing Then
  34.   Worksheets(customer).Cells(cnt, 1).Value = Worksheets("Oracle").Cells(I, 7).Value
  35.   Worksheets(customer).Cells(cnt, 2).Value = Worksheets("Oracle").Cells(I, 1).Value
  36.   Worksheets(customer).Cells(cnt, 4).Value = Worksheets("Oracle").Cells(I, 24).Value
  37.   Worksheets(customer).Cells(cnt, 5).Value = Worksheets("Oracle").Cells(I, 14).Value
  38.   Worksheets(customer).Cells(cnt, 6).Value = Worksheets("Oracle").Cells(I, 12).Value
  39.   Worksheets(customer).Cells(cnt, 7).Value = Worksheets("Oracle").Cells(I, 26).Value
  40.   Worksheets(customer).Cells(cnt, 8).Value = Worksheets("Oracle").Cells(I, 41).Value
  41.   Worksheets(customer).Cells(cnt, 9).Value = Split(Worksheets("Oracle").Cells(I, 42).Value, "/")(0)
  42.   Worksheets(customer).Cells(cnt, 10).Value = Worksheets("Oracle").Cells(I, 27).Value
  43.   Worksheets(customer).Cells(cnt, 11).Value = Worksheets("Oracle").Cells(I, 28).Value
  44.   Worksheets(customer).Cells(cnt, 13).Value = Worksheets("Oracle").Cells(I, 20).Value
  45.   Worksheets(customer).Cells(cnt, 14).Value = Worksheets("Oracle").Cells(I, 18).Value
  46.   Worksheets(customer).Cells(cnt, 15).Value = Worksheets("Oracle").Cells(I, 2).Value
  47.   Worksheets(customer).Cells(cnt, 16).Value = Worksheets("Oracle").Cells(I, 60).Value
  48.   If Trim(Worksheets("Oracle").Cells(I, 8).Value) = "" Then
  49.   Worksheets(customer).Cells(cnt, 3).Value = "NO"
  50.   Else
  51.   Worksheets(customer).Cells(cnt, 3).Value ="YES"
  52.   End If
  53.   cnt = cnt + 1
  54.    End If
  55.    End If
  56.   End If
  57.   
  58.   If Worksheets("Oracle").Cells(I, 5).Value = customer And Trim(Worksheets("Oracle").Cells(I, 20).Value) = "" Then
  59.   Set FRng = Worksheets(customer).Range("B:B").Find(Worksheets("Oracle").Cells(I, 1).Value, lookat:=xlWhole, SearchDirection:=xlPrevious)
  60.   If FRng Is Nothing Then
  61.   Set Rng = Worksheets("Filter").Range("A:A").Find(Worksheets("Oracle").Cells(I, 1), lookat:=xlWhole)
  62.   If Rng Is Nothing Then
  63.   Worksheets(customer).Cells(cnt, 1).Value = Worksheets("Oracle").Cells(I, 7).Value
  64.   Worksheets(customer).Cells(cnt, 2).Value = Worksheets("Oracle").Cells(I, 1).Value
  65.   Worksheets(customer).Cells(cnt, 4).Value = Worksheets("Oracle").Cells(I, 24).Value
  66.   Worksheets(customer).Cells(cnt, 5).Value = Worksheets("Oracle").Cells(I, 14).Value
  67.   Worksheets(customer).Cells(cnt, 6).Value = Worksheets("Oracle").Cells(I, 12).Value
  68.   Worksheets(customer).Cells(cnt, 7).Value = Worksheets("Oracle").Cells(I, 26).Value
  69.   Worksheets(customer).Cells(cnt, 8).Value = Worksheets("Oracle").Cells(I, 41).Value
  70.   Worksheets(customer).Cells(cnt, 10).Value = Worksheets("Oracle").Cells(I, 27).Value
  71.   Worksheets(customer).Cells(cnt, 11).Value = Worksheets("Oracle").Cells(I, 28).Value
  72.   Worksheets(customer).Cells(cnt, 13).Value = Worksheets("Oracle").Cells(I, 20).Value
  73.   Worksheets(customer).Cells(cnt, 14).Value = Worksheets("Oracle").Cells(I, 18).Value
  74.   Worksheets(customer).Cells(cnt, 15).Value = Worksheets("Oracle").Cells(I, 2).Value
  75.   Worksheets(customer).Cells(cnt, 16).Value = Worksheets("Oracle").Cells(I, 60).Value
  76.   If Trim(Worksheets("Oracle").Cells(I, 8).Value) = "" Then
  77.   Worksheets(customer).Cells(cnt, 3).Value = "NO"
  78.   Else
  79.   Worksheets(customer).Cells(cnt, 3).Value = "YES"
  80.   End If
  81.   If Trim(Worksheets("Oracle").Cells(I, 42).Value) = "" Then
  82.   Worksheets(customer).Cells(cnt, 9).Value = ""
  83.   Else
  84.   Worksheets(customer).Cells(cnt, 9).Value = Split(Worksheets("Oracle").Cells(I, 42).Value, "/")(0)
  85.   End If
  86.   cnt = cnt + 1
  87.    End If
  88.    End If
  89.    End If
  90.    
  91.   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
  92.   Set FRng = Worksheets(customer).Range("B:B").Find(Worksheets("Oracle").Cells(I, 1).Value, lookat:=xlWhole, SearchDirection:=xlPrevious)
  93.   If FRng Is Nothing Then
  94.   Set Rng = Worksheets("Filter").Range("A:A").Find(Worksheets("Oracle").Cells(I, 1), lookat:=xlWhole)
  95.   If Rng Is Nothing Then
  96.   Worksheets(customer).Cells(cnt, 1).Value = Worksheets("Oracle").Cells(I, 7).Value
  97.   Worksheets(customer).Cells(cnt, 2).Value = Worksheets("Oracle").Cells(I, 1).Value
  98.   Worksheets(customer).Cells(cnt, 4).Value = Worksheets("Oracle").Cells(I, 24).Value
  99.   Worksheets(customer).Cells(cnt, 5).Value = Worksheets("Oracle").Cells(I, 14).Value
  100.   Worksheets(customer).Cells(cnt, 6).Value = Worksheets("Oracle").Cells(I, 12).Value
  101.   Worksheets(customer).Cells(cnt, 7).Value = Worksheets("Oracle").Cells(I, 26).Value
  102.   Worksheets(customer).Cells(cnt, 8).Value = Worksheets("Oracle").Cells(I, 41).Value
  103.   Worksheets(customer).Cells(cnt, 9).Value = Split(Worksheets("Oracle").Cells(I, 42).Value, "/")(0)
  104.   Worksheets(customer).Cells(cnt, 10).Value = Worksheets("Oracle").Cells(I, 27).Value
  105.   Worksheets(customer).Cells(cnt, 11).Value = Worksheets("Oracle").Cells(I, 28).Value
  106.   Worksheets(customer).Cells(cnt, 13).Value = Worksheets("Oracle").Cells(I, 20).Value
  107.   Worksheets(customer).Cells(cnt, 14).Value = Worksheets("Oracle").Cells(I, 18).Value
  108.   Worksheets(customer).Cells(cnt, 15).Value = Worksheets("Oracle").Cells(I, 2).Value
  109.   Worksheets(customer).Cells(cnt, 16).Value = Worksheets("Oracle").Cells(I, 60).Value
  110.   If Trim(Worksheets("Oracle").Cells(I, 18).Value) <> "" Then Worksheets(customer).Cells(cnt, 12).Value = "ÒѸ¶¿î"
  111.   If Trim(Worksheets("Oracle").Cells(I, 8).Value) = "" Then
  112.   Worksheets(customer).Cells(cnt, 3).Value = "NO"
  113.   Else
  114.   Worksheets(customer).Cells(cnt, 3).Value = "YES"
  115.   End If
  116.   cnt = cnt + 1
  117.    End If
  118.    End If
  119.    End If
  120.   Next I

  121. For I = 2 To b
  122. If Worksheets("Client Detial").Cells(I, 1).Value = customer Then
  123. Worksheets(customer).Cells(5, 3).Value = Worksheets("Client Detial").Cells(I, 4).Value & " - " & Worksheets("Client Detial").Cells(I, 5).Value
  124. Worksheets(customer).Cells(6, 3).Value = Worksheets("Client Detial").Cells(I, 8).Value
  125. End If
  126. Next I

  127. e = Worksheets(customer).Range("A" & Worksheets(customer).Rows.Count).End(xlUp).Row

  128. With Worksheets(customer).Range(Cells(11, 1), Cells(e, 12))

  129. .Borders.LineStyle = 1

  130. .Borders.LineStyle = 1

  131. .Borders.ColorIndex = 0

  132. .BorderAround , 2, 0
  133. End With

  134. d = d + 1
  135. Loop Until d > c
  136. 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

TOP

¦^´_ 38# GBKEE


    §Ú¬O¥Îexcel2010ª©¡A¦b¦P¤@­Óexcel¤ºcreate ¤@­Ó·sªºsheets µM«á¦b³o­Ósheetsµe®Ø½u
excel2010ª©¬O§_¤]¤@¼Ë¡H

TOP

¥»©«³Ì«á¥Ñ GBKEE ©ó 2013-4-17 17:42 ½s¿è

¦^´_ 37# 198188

°Ñ¦Ò³o¸Ì
¥i¯àµo¥Í³o­Ó°ÝÃD¡A¦pªG¦³¤@­Ó©Î¦h­ÓÀx¦s®æ (Àx¦s®æ½d³ò) ªº°}¦C¤¤¥]§t³]©w¬°¥]§t¶W¹L 911 ­Ó¦r¤¸ªº¦r¤¸¦r¦ê¡C
·P®¦ªº¤ß......(¦b³Â»¶®a±Ú°Q½×°Ï.¥Î¤ß¾Ç²ß·|¦³¶i¨Bªº)
¦ý¸ê·½µL­­,«á´©¦³­­,  ¤@¤Ñ1¤¸ªºÃÙ§U,¤H¤H¦³¯à¤O.

TOP

¦^´_ 36# GBKEE

With Worksheets(customer).Range(Cells(11, 1), Cells(e, 12))
    ²{¦b³o¥y¥X²{RUN-TIME ERROR '1004' APPLICATION-DEFINED OR OBJECT-DEFINED ERROR

TOP

¦^´_ 35# 198188
Worksheets("customer") ³o¤u§@ªí§ä¤£¨ì???
§Aµ{¦¡½X©Ò¦bªºªº±M®×(¬¡­¶Ã¯)¬O»P Worksheets("customer")¦P¤@­Ó¬¡­¶Ã¯¶Ü???
¦p¤£¬O¶·«ü©ú(¬¡­¶Ã¯)->   Workbooks ("???.XLS").Worksheets("customer").............
·P®¦ªº¤ß......(¦b³Â»¶®a±Ú°Q½×°Ï.¥Î¤ß¾Ç²ß·|¦³¶i¨Bªº)
¦ý¸ê·½µL­­,«á´©¦³­­,  ¤@¤Ñ1¤¸ªºÃÙ§U,¤H¤H¦³¯à¤O.

TOP

        ÀR«ä¦Û¦b : ·O´d¨S¦³¼Ä¤H¡A´¼¼z¤£°_·Ð´o¡C
ªð¦^¦Cªí ¤W¤@¥DÃD