[µo°Ý] ¦p¦ó ¨ú¤£¦P¤u§@ªíªºÄæ¦ì­È,±a¤J¥t¤@¤u§@ªí°µ¹Bºâ

peter95

¤U¸ü (88.5 KB)

2016-3-10 23:54

¦p¦ó ¨ú¤£¦P¤u§@ªíªºÄæ¦ì­È,±a¤J¥t¤@¤u§@ªí°µ¹Bºâ


¹B§@­I´º:
§Ú¦³ ¥Ò¤A¤u§@ªí ©M¤@­Ó"²Î­p¤u§@ªí"
½Ð°Ý¦p¦ó±N "¥Ò¤u§@ªí"  "¤A¤u§@ªí" ªº¯S©wÄæ¦ì­È

±a¤J  "²Î­p¤u§@ªí" °µ¹Bºâ¨Ï¥Î

¥i¥H½Ð¤j®aÀ°§Ú¼g¤@­ÓVBA
Åý§Ú«ö¤@¤U ¥¦ª½±µ±a¤J
·PÁ¤j®aªºÀ°¦£

¤p§ÌªºÀÉ®×
Book1.rar (8.72 KB)

¤U¸ü¦¸¼Æ: 9

2016-3-10 23:56

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)

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

peter95

¦^´_ 5# lpk187


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

½Ð°Ýlpk187 ¤j¤j ³o¨â­Ó¨ú¥Xªº­È (¬Û¥[) µM«á¦b¥[¤W¤@­Ó­t­È  ¨Ã§â¥¦©T©w©ñ¦b ...©T©wGÄ檺Àx¦s®æ(±qG3¶}©l±N­È©ñ¤J..)

»¡©ú¤@¤U  "³o¨â­Ó¨ú¥Xªº­È (¬Û¥[) µM«á¦b¥[¤W¤@­Ó­t­È "
¤ñ¦p ¨ú¥X 192  336,,,-(192+336)=-528
¦A¦¸·PÁ§AªºÀ°¦£  ÁÂÁÂ

peter95

¦^´_ 9# lpk187

Lpk187¤j¤j«D±`·PÁ§Aªº¦^ÂÐ
¤p§Ì­è­è±µÄ²vbaªºªF¦è

¦³§A­Ì³o¨Ç°ª¤âÀ°¦£
¤p§Ì·P¿E¸U¤À
ÁÂÁ§A

§Ú¦Û¤v¥ý®ø¤Æ¤@¤U¡AÁÂÁÂ

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

peter95

¦^´_ 5# lpk187


Lpk187¤j¤j·PÁ§Aªº¦^ÂÐ

½Ð°Ý¤è«KÀ°§Ú¼g§Aªºµ{¦¡½Xµù¸Ñ¶Ü

¤p§Ì·PÁ§AªºÀ°¦£

yifan2599

¦^´_ 6# peter95


   §Úµo²{ ¼Ó¤Wªº ¨º­Ó¤èªk ¤ñ¸û§®,ÁÙ¤£¿à..

peter95

¦^´_ 4# yifan2599

½Ð°Ý¤j¤j ¥H¤Uªºµ{¦¡½X ªº·N«ä¤è«KÀ°§Ú¼gµù¸Ñ¶Ü??
¦A¦¸·PÁ§AªºÀ°¦£
ÁÂÁÂ
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

   s1 = Worksheets("¥Ò").Cells(1, 1).End(xlDown).Row
    s2 = Worksheets("¤A").Cells(1, 1).End(xlDown).Row
    s3 = Worksheets("²Î­p").Cells(1, 1).End(xlDown).Row
    s3_1 = Worksheets("²Î­p").Cells(10000, 8).End(xlUp).Row
    s3_2 = Worksheets("²Î­p").Cells(10000, 9).End(xlUp).Row
   
   
    w = MsgBox("½Ð°Ý­n±q²Î­p²Ä¤@¤Ñ°_¶}©l§ó·s", vbYesNoCancel, "!!")
   
    If w = vbYes Then
        xx = 2
    ElseIf w = vbNo Then
        xx = Application.Max(s3_1, s3_2)
    ElseIf w = vbCancel Then
        b = MsgBox("¨ú®ø§ó·s!", vbOKOnly, "!!")
        Exit Sub
    End If
   
   
    For x3 = xx To s3
        up = Worksheets("²Î­p").Cells(x3, 1)
   
        For x1 = 2 To s1
            If up = Worksheets("¥Ò").Cells(x1, 1) Then
                q1 = x1
            End If
        Next x1
   
        For x2 = 2 To s2
            If up = Worksheets("¤A").Cells(x2, 1) Then
                q2 = x2
            End If
        Next x2
        
        Worksheets("²Î­p").Cells(x3, 2) = Worksheets("¥Ò").Cells(q1, 12)
        Worksheets("²Î­p").Cells(x3, 3) = Worksheets("¤A").Cells(q2, 12)
    Next x3
   
   
    b = MsgBox("¸ê®ÆÂ^¨ú§¹¦¨!", vbOKOnly, "!!")

End Sub

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