Hsieh

¦^´_ 12# 198188
·|¥X²{¶W¥X°}¦C¯Á¤Þ¿ù»~¬O¦]¬°§A¦b¶}±Ò¨Ó·½ÀÉ¥H«á¡A¤@¯ë¼Ò²Õ¤ºµ{¦¡½X­Y¨S«ü©w¬¡­¶Ã¯¡A«h·|¥H·í«e§@¥Î¤¤ªº¬¡­¶Ã¯§@¬°¸Ó¬¡­¶Ã¯
³q±`§Ú·|³o»ò°µ¡A¤ñ¸û®e©ö§ä¥X¿ù»~ÂI

1. Sub ex()
   
2. Dim Sh As Worksheet, Rng As Range
   
3. fd = ThisWorkbook.Path & "\"  '¸ê®Æ¨Ó·½¥Ø¿ý
   
4. fs = "DOCS RECEIVED N RELEASED RECORD.xlsx" '¸ê®Æ¨Ó·½ÀÉ®×(§t°ÆÀɦW)
   
5. With Workbooks.Open(fd & fs)
   
6.   Set Sh = .Sheets("¦¬¥ó°O¿ý")
   
7.       With ThisWorkbook.Sheets("State")
   
8.          For Each A In .Range(.[A2], .Cells(.Rows.Count, 1).End(xlUp))
   
9.             Set Rng = Sh.Columns("D").Find(A, lookat:=xlWhole)
   
10.             If Not Rng Is Nothing Then
    
11.                If InStr(Rng.Offset(, 4), "OBL") > 0 Then _
    
12.                A.Offset(, 9) = Rng.Offset(, 4).Value Else A.Offset(, 9) = ""
    
13.             End If
    
14.          Next
    
15.       End With
    
16.     .Close
    
17. End With
    
18. End Sub

½Æ»s¥N½X

Hsieh

¦^´_ 14# 198188

1. Sub ex()
   
2. Dim Sh As Worksheet, Rng As Range, C As Range, Ar()
   
3. fd = ThisWorkbook.Path & "\"  '¸ê®Æ¨Ó·½¥Ø¿ý
   
4. fs = "DOCS RECEIVED N RELEASED RECORD.xlsx" '¸ê®Æ¨Ó·½ÀÉ®×(§t°ÆÀɦW)
   
5. With Workbooks.Open(fd & fs)
   
6.   Set Sh = .Sheets("¦¬¥ó°O¿ý")
   
7.       With ThisWorkbook.Sheets("State")
   
8.          For Each A In .Range(.[A2], .Cells(.Rows.Count, 1).End(xlUp))
   
9.             Set Rng = Sh.Columns("D").Find(A, lookat:=xlWhole)
   
10.             If Not Rng Is Nothing Then
    
11.                For Each C In Sh.Range(Rng, Sh.Cells(Sh.Rows.Count, 4).End(xlUp))
    
12.                   If C = A And InStr(UCase(C.Offset(, 4)), "OBL") > 0 Then
    
13.                      ReDim Preserve Ar(s)
    
14.                      Ar(s) = C.Offset(, 4)
    
15.                      s = s + 1
    
16.                   End If
    
17.                 Next
    
18.             If s > 0 Then A.Offset(, 9) = Join(Ar, "¡B"): Erase Ar: s = 0 Else A.Offset(, 9) = ""
    
19.             End If
    
20.          Next
    
21.       End With
    
22.     .Close
    
23. End With
    
24. End Sub

½Æ»s¥N½X

Hsieh

¦^´_ 19# 198188

¦pªG§Ú¼g³o¥y´À¥N¤W­±¨â¥yfs = "W:\PIHK\DOCS RECEIVED N RELEASED RECORD.xlsx"
²Ä5¦æ³o¥y
With Workbooks.Open(fd & fs)
´N­n§ï¦¨
With Workbooks.Open(fs)

Join(Ar, "¡B"): Erase ³o¥y¬O¤°»ò·N«ä¡H
µ{¦¡»yªk¤£¯à¥uŪ¤@¥b¡A¦b¦P¤@¦æ±Ô­z¨Ï¥Î«_¸¹¡A¬Û·í©ó¨â¦æ±Ô­z
Join(Ar, "¡B") ¡÷  ·|±o¨ì°}¦C¤¸¯À¥Î¹y¸¹¡B³s±µªº¦r¦ê
Erase Ar ¡÷  ¬O²MªÅ°}¦C

µ{¦¡¼g±o¹ï¤£¹ï¡A°õ¦æ¤@¤U´Nª¾¹Dµ²ªG°Õ

Hsieh

¦^´_ 22# 198188
³o¼Ëªºµ{¦¡»P¼Ó¤Wµ{¦¡½X¤ñ¸û¬Ý¬ÝÀ³¸Ó´N®e©ö¤F¸Ñ

1. Sub ex()
   
2. Dim Sh As Worksheet, Rng As Range, C As Range, Ar()
   
3. fd = ThisWorkbook.Path & "\"  '¸ê®Æ¨Ó·½¥Ø¿ý
   
4. fs = "DOCS RECEIVED N RELEASED RECORD.xlsx" '¸ê®Æ¨Ó·½ÀÉ®×(§t°ÆÀɦW)
   
5. With Workbooks.Open(fd & fs)
   
6.   Set Sh = .Sheets("¦¬¥ó°O¿ý")
   
7.       With ThisWorkbook.Sheets("State")
   
8.          For Each A In .Range(.[A2], .Cells(.Rows.Count, 1).End(xlUp))
   
9.             Set Rng = Sh.Columns("D").Find(A, lookat:=xlWhole)
   
10.             If Not Rng Is Nothing Then
    
11.                For Each C In Sh.Range(Rng, Sh.Cells(Sh.Rows.Count, 4).End(xlUp))
    
12.                   If C = A And InStr(UCase(C.Offset(, 4).MergeArea(1)), "OBL") > 0 Then
    
13.                      ReDim Preserve Ar(s)
    
14.                      Ar(s) = C.Offset(, 4).MergeArea(1)
    
15.                      s = s + 1
    
16.                   End If
    
17.                 Next
    
18.             If s > 0 And A.Offset(, 9) = "" Then
    
19.                A.Offset(, 9) = Join(Ar, "¡B")
    
20.                Erase Ar
    
21.                s = 0
    
22.                   Else
    
23.                A.Offset(, 9) = ""
    
24.             End If
    
25.             End If
    
26.          Next
    
27.       End With
    
28.     .Close
    
29. End With
    
30. End Sub

½Æ»s¥N½X

Hsieh

¦^´_ 24# 198188
µ½¥ÎF1»¡©ú»P³v¦æ°»¿ù¤~¯à¹ï»yªk¹ý©³¤F¸Ñ
§A°Ý¦X¨ÖÀx¦s®æ¬O§_¥i¦æ?
ªí¥Ü§A®Ú¥»¨S¦³°õ¦æ´ú¸Õ
¨S¦³«i®ð´ú¸Õ¥u·|Åý§A¥Ã»·°±¹y
°£«D¦³ÃÒ©ú¼Ó¤Wµ{¦¡½XµLªk¹F¦¨»Ý¨D
¥B»¡©ú²M·¡°ÝÃDÂI¡A§_«h¦¹°ÝÃD±N¤£¦A¦^À³

Hsieh

¦^´_ 26# 198188
§A«ç»ò´ú¸Õªº?

¤U¸ü (747.03 KB)

2013-3-10 10:18

Hsieh

¦^´_ 32# 198188


    À³¸Ó³o¥y¿ù»~
Worksheets("OHC").Range("G" & i).Font.ColorIndex = RGB(217, 24, 9)
ColorIndex À³¸Ó¬O¼Æ­È¤£¥i¨Ï¥ÎRGB
¥i§ï¦¨
Worksheets("OHC").Range("G" & i).Font.Color = RGB(217, 24, 9)