Hsieh

¦^´_ 1# 198188


    ¶i¶¥¿z¿ï§Y¥i

Hsieh

¦^´_ 3# 198188


    §A¬O­n§âJPMªº¸ê®Æ¦C½Æ»s¹L¥h¤£¬O¶Ü?
¨º´N¿ý»s¶i¶¥¿z¿ï¨ú±oµ{¦¡½X´N¦n¤F
­Y¤£·Q¦h¥X·Ç«hÄæ¦ì¡A¨º¥Î¥H¤U¥N½X
±NJPM¥Î¿ù»~­È¤½¦¡¨ú¥N
µM«á½Æ»s³o¨Ç¦C¶K¨ì¥Ø¼Ð¦ì¸m

1. Sub nn()
   
2. With ¤u§@ªí1
   
3. .Range("B:B").Replace "JPM", "=1/0", xlWhole
   
4. Set Rng = .Range("B:B").SpecialCells(xlCellTypeFormulas, 16)
   
5. Rng.Value = "JPM"
   
6. Rng.EntireRow.Copy Sheets("JPM").[A2]
   
7. End With
   
8. End Sub

½Æ»s¥N½X

Hsieh

¦^´_ 6# 198188

1. Sub nn()
   
2. With ¤u§@ªí1
   
3. If Application.CountIf(.Range("B:B"), "JPM") > 0 Then '§PÂ_BÄæ¬O§_¦³JPM
   
4. .Range("B:B").Replace "JPM", "=1/0", xlWhole '±NJPM¥H¤½¦¡¨ú¥N
   
5. Set Rng = .Range("B:B").SpecialCells(xlCellTypeFormulas, 16) '±N¤½¦¡¬°¿ù»~­ÈªºÀx¦s®æ³]¬°ÅܼÆ
   
6. Rng.Value = "JPM" '±N¤½¦¡Á٭즨JPM
   
7. Sheets("JPM").UsedRange.Offset(1).Clear '±NJPM¤u§@ªí¤º®e²MªÅ
   
8. Rng.EntireRow.Copy Sheets("JPM").[A2] '±NBÄ欰JPMªº¦C½Æ»s¶K¨ìJPM¤u§@ªí
   
9. End If
   
10. End With
    
11. End Sub

½Æ»s¥N½X

Hsieh

¦^´_ 9# 198188

1. Sub nn()
   
2. With Sheets("Sheet1") '§ï¦¨¥¿½T¤u§@ªí¦WºÙ
   
3. If Application.CountIf(.Range("B:B"), "JPM") > 0 Then '§PÂ_BÄæ¬O§_¦³JPM
   
4. .Range("B:B").Replace "JPM", "=1/0", xlWhole '±NJPM¥H¤½¦¡¨ú¥N
   
5. Set Rng = .Range("B:B").SpecialCells(xlCellTypeFormulas, 16) '±N¤½¦¡¬°¿ù»~­ÈªºÀx¦s®æ³]¬°ÅܼÆ
   
6. Rng.Value = "JPM" '±N¤½¦¡Á٭즨JPM
   
7. Sheets("JPM").UsedRange.Offset(1).Clear '±NJPM¤u§@ªí¤º®e²MªÅ
   
8. Rng.EntireRow.Copy Sheets("JPM").[A2] '±NBÄ欰JPMªº¦C½Æ»s¶K¨ìJPM¤u§@ªí
   
9. End If
   
10. End With
    
11. End Sub

½Æ»s¥N½X

°ÝÃD¤G
¶}±ÒÀÉ®×
fs = "C:\user\destop\outstanding payment.xlsx"
Set wb = Workbooks.Open(fs)
For j=1 To ....
i = Application.Match(Sheet1.Cells(1, j), wb.Sheets("Sheet2").[A:A], 0)
...
...
...
Next
wb.Close 0

Hsieh

¦^´_ 11# 198188

1. Sub nn()
   
2. Dim Ay(), Rng As Range, m$, A As Range, r&, Ar
   
3. With Sheets("Sheet1") '§ï¦¨¥¿½T¤u§@ªí¦WºÙ
   
4. If Application.CountIf(.Range("B:B"), "JPM") > 0 Then '§PÂ_BÄæ¬O§_¦³JPM
   
5. .Range("B:B").Replace "JPM", "=1/0", xlWhole '±NJPM¥H¤½¦¡¨ú¥N
   
6. Set Rng = .Range("B:B").SpecialCells(xlCellTypeFormulas, 16) '±N¤½¦¡¬°¿ù»~­ÈªºÀx¦s®æ³]¬°ÅܼÆ
   
7. Rng.Value = "JPM" '±N¤½¦¡Á٭즨JPM
   
8. For Each A In Rng
   
9. r = A.Row
   
10. m = .Cells(r, "U") & "¡B" & .Cells(r, "V") & "¡B" & .Cells(r, "W")
    
11. Ar = Array(.Cells(r, "S").Value, .Cells(r, "T").Value, .Cells(r, "C").Value, .Cells(r, "AA").Value, .Cells(r, "D").Value, .Cells(r, "AB").Value, _
    
12. .Cells(r, "AC").Value, .Cells(r, "AD").Value, .Cells(r, "AE").Value, .Cells(r, "AF").Value, .Cells(r, "F").Value, m, .Cells(r, "X").Value, .Cells(r, "Y").Value, .Cells(r, "Z").Value)
    
13. ReDim Preserve Ay(s)
    
14. Ay(s) = Ar
    
15. s = s + 1
    
16. Next
    
17. Sheets("JPM").UsedRange.Offset(1).Clear '±NJPM¤u§@ªí¤º®e²MªÅ
    
18. If s > 0 Then Sheets("JPM").[A2].Resize(s, UBound(Ar) + 1) = Application.Transpose(Application.Transpose(Ay)) '±N°}¦C¼g¨ìJPM¤u§@ªí
    
19. End If
    
20. End With
    
21. End Sub

½Æ»s¥N½X

Match¨ç¼Æ¦pªG§ä¤£¨ì²Å¦X¸ê®Æ´N·|¥X¿ù

Hsieh

¦^´_ 14# 198188
½M¤lºN¶H
fs¼g¤J¦r¦ê¤£¥i¯à¥X¿ù¡A´Nºâ¬O¸ô®|¿ù»~¤]¤£¬O¦b¸Ó¦æ¥X¿ù
½Ð§â°ÝÃD»¡©ú²M·¡¡A§_«h°ÝÃD¤£¦A¦^´_

Hsieh

¦^´_ 18# 198188
´£°Ý®É½Ð´y­z§Aªº»Ý¨D
¥ú±q§Aªºµ{¦¡½X¥h²q§Aªº»Ý¨D·|³y¦¨«Ü¤jªº®t²§
¬Ý¬Ý¬O§_¦X¥G§Aªº»Ý¨D

1. Sub sample()
   
2. Dim FRng As Range
   
3. Dim A As Range, Rng As Range
   
4. fs = "C:\Documents and Settings\USER\®à­±\HK ETA update.xlsx"
   
5. 'fs = ThisWorkbook.Path & "\HK ETA update.xlsx"'¦P¤@¥Ø¿ý®É¨Ï¥Î
   
6. Set wb = Workbooks.Open(fs)
   
7. With ThisWorkbook.Worksheets("2012")
   
8. For Each A In .Range(.[A2], .Range("A1").End(xlDown))
   
9.    Set FRng = wb.Sheets("­»´ä&®ü¨¾³æ").Range("A:A").Find(A, lookat:=xlWhole)
   
10.    If Not FRng Is Nothing Then
    
11.       If FRng.Offset(, 11) <> A.Offset(, 3) Then
    
12.          A.Offset(, 3) = FRng.Offset(, 11).Value 'Åý2012ªºDÄæµ¥©ó­»´ä&®ü¨¾³æªºLÄæ
    
13.          If Rng Is Nothing Then Set Rng = A.Offset(, 3) Else Set Rng = Union(Rng, A.Offset(, 3))
    
14.       End If
    
15.    End If
    
16.    Set FRng = Nothing
    
17. Next
    
18.         If Not Rng Is Nothing Then Rng.Interior.Color = RGB(255, 200, 255)
    
19. End With
    
20. wb.Close 0
    
21. End Sub

½Æ»s¥N½X