¦p¦ó¥i¥HÅý¤£¬O"JPM"¤£Åã¥Ü¥X¨Ó¡A¤]¤£·|¯d¤@¦æªÅ®æ¡H

198188

Book1.rar (10.67 KB)

¤U¸ü¦¸¼Æ: 17

2012-11-11 02:39

Sub JPM()
   Dim i As Integer, j As Integer, k As Integer, l As Integer
   Dim rowC As Integer
   Dim rB As Range
   Dim data() As String
   Dim found As Boolean

   '¥ý±N AK:AR ªº¸ê®Æ²M°£
   Worksheets("jpm").[A2:O65536].ClearContents

   '­pºâ¦h¤Öµ§¸ê®Æ­n³B²z
   rowC = Sheets(1).Range("A1").CurrentRegion.Rows.Count
   '¥ý¼È¦s¸ê®Æ¡A¥[³t³B²z
   Set rB = Sheets(1).Range(Cells(1, 1), Cells(rowC, 32))
   ReDim data(rowC, 32)

   k = 0
   For i = 1 To rowC '³B²z¸ê®Æ
       j = 1
       found = False
       While (j <= k) And (found = False) '¤ñ¹ï¦³¨S¦³¥X²{¹L
          If rB(i, 19) = data(j, 19) And rB(i, 20) = data(j, 20) And rB(i, 3) = data(j, 3) Then
             found = True
             data(j, 3) = rB(i, 3)
             data(j, 4) = rB(i, 4)
             data(j, 6) = rB(i, 6)
             data(j, 19) = rB(i, 19) + "¡B" + rB(i, 19)
             data(j, 20) = rB(i, 20) + "¡B" + rB(i, 20)
             data(j, 21) = data(j, 21) + "¡B" + rB(i, 21)
             data(j, 22) = data(j, 22) + "¡B" + rB(i, 22)
             data(j, 23) = data(j, 23) + "¡B" + rB(i, 23)
             data(j, 24) = data(j, 24) + "¡B" + rB(i, 24)
             data(j, 25) = data(j, 25) + "¡B" + rB(i, 25)
             data(j, 26) = data(j, 26) + "¡B" + rB(i, 26)
             data(j, 27) = rB(i, 27)
             data(j, 28) = rB(i, 28)
             data(j, 29) = rB(i, 29)
             data(j, 30) = rB(i, 30)
             data(j, 31) = rB(i, 31)
             data(j, 32) = rB(i, 32)
        End If
          j = j + 1
       Wend

       If found = False Then  '¨S¦³¥X²{¹L¥[¤J·s¸ê®Æ
          k = k + 1
          data(k, 3) = rB(i, 3)
          data(k, 4) = rB(i, 4)
          data(k, 6) = rB(i, 6)
          data(k, 19) = rB(i, 19)
          data(k, 20) = rB(i, 20)
          data(k, 21) = rB(i, 21)
          data(k, 22) = rB(i, 22)
          data(k, 23) = rB(i, 23)
          data(k, 24) = rB(i, 24)
          data(k, 25) = rB(i, 25)
          data(k, 26) = rB(i, 26)
          data(k, 27) = rB(i, 27)
          data(k, 28) = rB(i, 28)
          data(k, 29) = rB(i, 29)
          data(k, 30) = rB(i, 30)
          data(k, 31) = rB(i, 31)
          data(k, 32) = rB(i, 32)
     End If
   Next i

       l = 1
   For i = 1 To k '¦C¦L¸ê®Æ
      
       If Range("B" & i + 1).Value = "JPM" Then
      
       Sheets("JPM").Cells(l, 1) = data(i, 19)
       Sheets("JPM").Cells(l, 2) = data(i, 20)
       Sheets("JPM").Cells(l, 3) = data(i, 3)
       Sheets("JPM").Cells(l, 4) = data(i, 27)
       Sheets("JPM").Cells(l, 5) = data(i, 4)
       Sheets("JPM").Cells(l, 6) = data(i, 28)
       Sheets("JPM").Cells(l, 7) = data(i, 29)
       Sheets("JPM").Cells(l, 8) = data(i, 30)
       Sheets("JPM").Cells(l, 9) = data(i, 31)
       Sheets("JPM").Cells(l, 10) = data(i, 32)
       Sheets("JPM").Cells(l, 11) = data(i, 6)
       Sheets("JPM").Cells(l, 12) = data(i, 21) + "¡B" + data(i, 22) + "¡B" + data(i, 23)
       Sheets("JPM").Cells(l, 13) = data(i, 24)
       Sheets("JPM").Cells(l, 14) = data(i, 25)
       Sheets("JPM").Cells(l, 15) = data(i, 26)
       End If
       l = l + 1
      Next i

MsgBox ("Sucess")

End Sub

¦p¦ó¥i¥HÅý¤£¬O"JPM"¤£Åã¥Ü¥X¨Ó¡A¤]¤£·|¯d¤@¦æªÅ®æ¡H

Hsieh

¦^´_ 1# 198188


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

198188

¦^´_ 2# Hsieh

vba ¨S¦³¿ìªkÅý¥¦¤£¥X²{¶Ü¡H

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

c_c_lai

¦^´_ 1# 198188
¤£ª¾¹D³o¬O§_¬O§Aªº»Ý¨D¡H

1. Sub Test()
   
2.     Sheets("JPM").Visible = False
   
3. End Sub
   
4. 
   
5. Sub Test2()
   
6.     Sheets("JPM").Visible = True
   
7. End Sub

½Æ»s¥N½X

198188

¦^´_ 4# Hsieh


    04.Set Rng = .Range("B:B").SpecialCells(xlCellTypeFormulas, 16) ¥X²{error, ±N§ÚDATA BASEªº JPMÂন1/0,¦ý¦bjpm worksheet¤º´N¨S¦³copy ¸ê®Æ¹L¥h¡I

¥t¥~JPM  worksheetªº¸ê®Æ¬O¶¶§Ç±NDATA BASEªº JPM¸ê®Æ§Û¹L¨Ó¡A¦ýÂ\¦ì¬O¤£¤@¼Ë

DATA BASE (SHEET1)
Äæ¼Æ¥ÑA :Z
ITEM NO / STATE / SO / BUYER / AGENT / ETA /RECEIVE DATE/ RECEIVE TRACKING NO/ RECEIVE OBL/RECEIVE OHC/RECEIVE OTHER DOCS/RECEIVE REMARK/SEND DATE/SEND TRACKING NO/ SEND OBL/SEND OHC/SEND OTHER DOCS/SEND REMARK/ JPM DATE/JPM REF NO/JPM OBL/JPM OHC/ JPM OTHER DOCS/JPM REMARK/JPM US ARRANGE PAYMENT ON/JPM HK PICK UP ON

JPM WORKSHEET (JPM)¥uÅã¥Ü³o¨Ç¸ê®Æ
Äæ¼Æ¥ÑA : G
JPM DATE / JPM REF NO / SO/JPM DOCS LIST(JPM OBL, JPM OHC, JPM OTHER DOCS ¦X¨Ö¡^/JPM REMARK/JPM US ARRANGE PAYMENT ON/JPM HK PICK UP ON

198188

¦^´_ 5# c_c_lai


    ³o­Óµ{¦¡¨S¦³¤Ï¬M¡A¬O§_ÁÙ¤í¯Ê¤@¨Ç¸ê®Æ¡H

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

198188

¦^´_ 8# Hsieh


result.rar (486.07 KB)

¤U¸ü¦¸¼Æ: 4

2012-11-13 14:36

ÁÙ¬O¨S¦³Åã¥Ü¥X¨Ó. JPM WORKSHEETªÅ¥Õ¤@¤ù~
¥t¥~·Q°Ý
Sub sample()

Dim LastRec As Integer
Dim j As Integer
Dim i As Integer
Dim l As Integer
Dim data() As Range
l = 1

Worksheets("Sheet1").Range("A1").Select
ActiveCell.End(xlDown).Select
     LastRec = ActiveCell.Row
     

For j = 1 To LastRec

i = Application.Match(Sheet1.Cells(1, j), "C:\user\destop\[outstanding payment]Sheet2'!.Range("A:A"), 0) §Ú·QŪ¨ú®à­±¥t¥~¤@­Óexcelªº¸ê®Æ¡A³o¥y¬O¤£¬O¦³°ÝÃD¡H

If Sheet1.Cells(i, 2).Value <> Sheet2.Cells(i, 2).Value Then

Sheet1.Cells(i, 2).Value = Sheet2.Cells(i, 2).Value
Sheet1.Cells(i, 2).Interior.Color = RGB(255, 200, 255)
End If

Next j

End Sub

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