ªð¦^¦Cªí ¤W¤@¥DÃD µo©«

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

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

ask.jpg
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)
¾Ç²ß ¾Ç²ß ¤@ª½¾Ç²ß

¦^´_ 1# peter95


    °Ñ¦Ò¬Ý¬Ýªþ¥[ÀÉ

Book1.zip (23.3 KB)

¤H¥Í¤£¤@©w²y²y¬O¦n²y¡A¦ý¬O¦³¾ú½mªº±j¥´ªÌ¡AÀH®É³£¥i¥H´§´Î¡C

TOP

¦^´_ 2# yifan2599

·PÁ¤j¤jªº¦^ÂÐ
¤p§Ì«ÜêÉ ¦]¬°§Ú¬O¤p¾Ç¥Íµ¥¯Å
µLªk¤U¸üªþÀÉ

¥i¥H½Ð¤j¤j ¶K·½½X ¶Ü¡H

¤£¦n·N«ä ³Â·Ð§A¤F
ÁÂÁ§AªºÀ°¦£
¾Ç²ß ¾Ç²ß ¤@ª½¾Ç²ß

TOP

¤H¥Í¤£¤@©w²y²y¬O¦n²y¡A¦ý¬O¦³¾ú½mªº±j¥´ªÌ¡AÀH®É³£¥i¥H´§´Î¡C

TOP

¦^´_ 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

TOP

¦^´_ 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
¾Ç²ß ¾Ç²ß ¤@ª½¾Ç²ß

TOP

¦^´_ 6# peter95


   §Úµo²{ ¼Ó¤Wªº ¨º­Ó¤èªk ¤ñ¸û§®,ÁÙ¤£¿à..
¤H¥Í¤£¤@©w²y²y¬O¦n²y¡A¦ý¬O¦³¾ú½mªº±j¥´ªÌ¡AÀH®É³£¥i¥H´§´Î¡C

TOP

¦^´_ 5# lpk187


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

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

¤p§Ì·PÁ§AªºÀ°¦£
¾Ç²ß ¾Ç²ß ¤@ª½¾Ç²ß

TOP

¥»©«³Ì«á¥Ñ lpk187 ©ó 2016-3-12 18:29 ½s¿è

¦^´_ 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

TOP

¦^´_ 9# lpk187

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

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

§Ú¦Û¤v¥ý®ø¤Æ¤@¤U¡AÁÂÁÂ
¾Ç²ß ¾Ç²ß ¤@ª½¾Ç²ß

TOP

        ÀR«ä¦Û¦b : ¤Hªº¤ß¦a¬O¤@²¥¥Ð¡A¤g¦a¨S¦³¼½¤U¦nºØ¤l¡A¤]ªø¤£¥X¦nªºªG¹ê¡C -
ªð¦^¦Cªí ¤W¤@¥DÃD