lpk187

¦^´_ 1# peter95

1. Sub Â^¨ú()
   
2.     Dim Rng As Range, aRng As Range, bRng As Range
   
3.     With Sheets("²Î­p")
   
4.         .Range("B2:C" & .Cells(Rows.Count, 1).End(xlUp).Row).ClearContents
   
5.         For Each Rng In .Range("A2:A" & .Cells(Rows.Count, 1).End(xlUp).Row)
   
6.             Set aRng = Sheets("¥Ò").Columns(1).Find(Rng.Value, LookIn:=xlFormulas, LookAt:=xlWhole, SearchDirection:=2)
   
7.             Set bRng = Sheets("¤A").Columns(1).Find(Rng.Value, LookIn:=xlFormulas, LookAt:=xlWhole, SearchDirection:=2)
   
8.             If Not aRng Is Nothing Then Rng.Offset(, 1) = aRng.Offset(, 11)
   
9.             If Not aRng Is Nothing Then Rng.Offset(, 2) = bRng.Offset(, 11)
   
10.         Next
    
11.     End With
    
12. End Sub

½Æ»s¥N½X

lpk187

¦^´_ 8# peter95

¥H¤U¥N½X½ÐCopy¨ì¼Ò²Õ¤¤¡A¨Ã«öF8³v¨B°õ¦æ¡A·|§ó®e©ö²z¸Ñ

1. Sub Â^¨ú()
   
2.     '¤À§O«Å§iÅܼƬ°Range(Àx¦s®æ)ª«¥ó¡ARng¬°"²Î­p"¤u§@ªíªºAÄæ¨C¦¸´`Àô¤¤©ÒŪ¨úªºÀx¦s®æ¡A
   
3.     'aRng¬°"¥Ò"¤u§@ªí¬d´M«á§ä¨ìªºÀx¦s®æ¡AbRng¬°"¤A"¤u§@ªí¬d´M«á§ä¨ìªºÀx¦s®æ
   
4.     'Range¨ì©³¬O¤°»ò¡H½Ð¬d¬Ý»¡©ú
   
5.     Dim Rng As Range, aRng As Range, bRng As Range
   
6.     With Sheets("²Î­p") 'with ¬°Â²¤Æ¥N½X¥Î¡A¥H¤Uªº¥N½X¤¤­Y¦³¥H"."¬°¶}©lªº¥N½X¡A¨ä«e­±³£·|¥[¤W"Sheets("²Î­p")"³o¦ê
   
7.     '¨Ò¦p".Cells"¡A¨ä§¹¾ã¦r¥yÀ³¸Ó¬O "Sheets("²Î­p").Cells(...."
   
8.         .Range("B2:C" & .Cells(Rows.Count, 1).End(xlUp).Row).ClearContents '²M°£¤º®e(­È)
   
9.         '¥H±qA2¶}©l¨ì³Ì«á¤@­Ó¦ì¸m¬°½d³ò(³o¸Ì¬d¨ìªº¬OA11)¡A°µ°j°éŪ¨ìªºÀx¦s®æ¬°Rng¡A¨Ò¦p²Ä¤@­ÓŪªº¦ì§}¬°A2¡A¨ä­È(Value)¬°"2¤ë25¤é"
   
10.         'µM«á¥H¨ä­È"2¤ë25¤é"¥h°µ·j´M(.Find)"¥Ò"¤u§@ªí©M"¤A"¤u§@ªí¡A¨Ã§ä¨ì¨äÀx¦s®æ¡A·j´M¤é´Á¦b³o¸Ì¤£¯à¥Î­È¥h§ä¡A³o¸Ì¥²¶·­n¥Î¤½¦¡¥h§ä"LookIn:=xlFormulas"
    
11.         '³o¸Ìªº°Ñ¼Æ¦p¦ó¨Ï¥Î¡A½Ð°Ñ¦ÒVisual Basic ªº»¡©ú"Range.Find"
    
12.         For Each Rng In .Range("A2:A" & .Cells(Rows.Count, 1).End(xlUp).Row)
    
13.             Set aRng = Sheets("¥Ò").Columns(1).Find(Rng.Value, LookIn:=xlFormulas, LookAt:=xlWhole, SearchDirection:=2) '·j´M(.Find)"¥Ò"¤u§@ªí¡A¨Ã³]©w¬°aRngª«¥ó
    
14.             Set bRng = Sheets("¤A").Columns(1).Find(Rng.Value, LookIn:=xlFormulas, LookAt:=xlWhole, SearchDirection:=2) '·j´M(.Find)"¤A"¤u§@ªí¡A¨Ã³]©w¬°bRngª«¥ó
    
15.             '­Y¨S¦³¬dÀò¨ì Rng ª«¥ó¡A«haRng©ÎbRng·|¬°Nothing¡A©Ò¥H¤U­±»y¥yªº¸ÑÄÀ¬°¦pªGaRng¤£¬ONothingªº¸Ü¡A
    
16.             '¥H²Ä¤@¦¸´`Àôªº­È¬°¨Ò¡A·|¦bRng.Offset(, 1)©ñ¤JaRng.Offset(, 11)ªº­È¡A¤]´N¬O·|¦b²Î­pªºB2©ñ¶i¥ÒªºK2­È¡AbRng¥ç¦P¡A·|¦bC2©ñ¶i¤AªºK2­È¡A©I~~~
    
17.             If Not aRng Is Nothing Then Rng.Offset(, 1) = aRng.Offset(, 11) '
    
18.             If Not bRng Is Nothing Then Rng.Offset(, 2) = bRng.Offset(, 11)
    
19.         Next
    
20.     End With
    
21. End Sub

½Æ»s¥N½X

lpk187

¦^´_ 11# peter95

¤U¹Ï¬OÅý§AÁA¸ÑRangeª«¥óªºOffsetÄÝ©Ê¡G¹Bºâ¦¡.Offset(¦Cªº°¾²¾¶q, Ä檺°¾²¾¶q)

·|Åý§A§ó®e©öÁA¸Ñµ{¦¡«ç»ò¹B§@ªº

¤U¸ü (49.06 KB)

2016-3-16 08:47

1. Option Explicit
   
2. 
   
3. Sub Â^¨ú()
   
4.     Dim Rng As Range, aRng As Range, bRng As Range
   
5.     With Sheets("²Î­p")
   
6.         .Range("B2:C" & .Cells(Rows.Count, 1).End(xlUp).Row).ClearContents
   
7.         For Each Rng In .Range("A2:A" & .Cells(Rows.Count, 1).End(xlUp).Row)
   
8.             Set aRng = Sheets("¥Ò").Columns(1).Find(Rng.Value, LookIn:=xlFormulas, LookAt:=xlWhole, SearchDirection:=2)
   
9.             Set bRng = Sheets("¤A").Columns(1).Find(Rng.Value, LookIn:=xlFormulas, LookAt:=xlWhole, SearchDirection:=2)
   
10.             If Not aRng Is Nothing Then Rng.Offset(, 1) = aRng.Offset(, 11)
    
11.             If Not bRng Is Nothing Then Rng.Offset(, 2) = bRng.Offset(, 11)
    
12.             Rng.Offset(, 6) = -(Rng.Offset(, 1) + Rng.Offset(, 1))
    
13.         Next
    
14.     End With
    
15. End Sub

½Æ»s¥N½X

lpk187

¦^´_ 11# peter95


    ÁÙ¦³§Ú²Ä¤@¦¸PO¦¸ªº¥N½X¤¤
If Not aRng Is Nothing Then Rng.Offset(, 1) = aRng.Offset(, 11)
If Not aRng Is Nothing Then Rng.Offset(, 2) = bRng.Offset(, 11)<<=³o¥y¬O¿ùªº
À³¸Ó­×¥¿¬°
If Not bRng Is Nothing Then Rng.Offset(, 2) = bRng.Offset(, 11)