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

[µo°Ý] ¦r¨å¨Ï¥Î°ÝÃD

[µo°Ý] ¦r¨å¨Ï¥Î°ÝÃD

¤u§@¬O³o¼Ëªº
¥Î¤u§@ªí2ªº"PN"Äæ¦ì¥h¸ò¤u§@ªí1ªº"PN"Äæ¦ì¤ñ¹ï¡A¦pªG¦³¤ñ¹ï¨ì¬Û¦Pªº¡A´N§â¨º­ÓPN±qCÄæ¨ìLÄ檺¼Æ­È¶Ç¦^¨ì¤u§@ªí2¬Û¦PPNªº¹ïÀ³¦ì¸m¡Aµ{¦¡¤@ª½¨S¿ìªk¥¿½T°õ¦æ¡A¤£¹L¹ê¦b§ä¤£¨ì­þ¸Ì¦³°ÝÃD¡A¯àÀ°¦£¬Ý¤@¤U°ÝÃD¥X¦b­þ¶Ü?
test.rar (13.41 KB)
Jess

¦^´_ 1# jesscc
µ{¦¡½X­×§ï¦p¤U¡G
  1. Option Base 1
  2. Sub test()

  3. With Sheets("¤u§@ªí1")
  4. Set D = CreateObject("Scripting.Dictionary")

  5. For Each B In .Range(.[B6], .[B100].End(xlUp))
  6. Dim Ar(10)
  7.     For p = 1 To 10
  8.     Ar(p) = B.Offset(, p).Value
  9.     Next p
  10.     D(B & "") = Ar
  11. Next
  12. End With


  13. With Sheets("¤u§@ªí2")
  14. .[F7:O1000].ClearContents
  15.     For Each E In .Range(.[E7], .[E100].End(xlUp))
  16.       If D.EXISTS(E & "") Then
  17.       E.Offset(, 1).Resize(, 10) = D(E & "")
  18.       Else: E.Offset(, 1) = "¬dµL¦¹ PN"
  19.       End If
  20.     Next
  21. End With


  22. End Sub
½Æ»s¥N½X

TOP

½Ð°ÝK¤j¡A§Ú¤§«e¨ä¥Lªº¤u§@¡A¦r¨åµ{¦¡¶}ÀY³£¨S¥[³o¬q "Option Base 1"¡A ±N°}¦C¯Á¤Þ­È¤U­­³]¬°1¡A¦ý³£¥i¥H¥¿±`°õ¦æ¡A¬°¤°»ò¦b³o¸Ì¨S¦³¥[´N·|°õ¦æ¿ù»~?
Jess

TOP

¦^´_ 3# jesscc


    With Sheets("¤u§@ªí2")
.[F7:O1000].ClearContents
    For Each E In .Range(.[E7], .[E100].End(xlUp))
      If D.EXISTS(B & "") Then
      E.Offset(, 1).Resize(, 10) = D(B & "")
      Else: E.Offset(, 1) = "¬dµL¦¹ PN"
      End If
    Next
End With

TOP

B change as E

TOP

B change as E
jackyq µoªí©ó 2018-10-21 07:38


¤w§ï¡A¥i¥¿±`°õ¦æ¡A¦ý§ÚªººÃ°Ý¬O¦pªG§â²Ä¤@¬q³o¥y "Option Base 1" ®³±¼¡A¾ã­Ó¸ê®Æ¶Ç¦^µ²ªG·|¦V¥k°¾¤@Äæ¡A§Ú¥uª¾¹D³o¬O°}¦C¯Á¤Þ¤U­­­Èªº°ÝÃD¡A¦pªG¦b¤£³]©w¤U­­­Èªº±¡§Î¤U¡A­n¦p¦ó§ï¼g­ì¨Óªº°}¦C±Æ¦C?
Jess

TOP

¦^´_ 6# jesscc


    With Sheets("¤u§@ªí1")
Set D = CreateObject("Scripting.Dictionary")

For Each B In .Range(.[B6], .[B100].End(xlUp))
Dim Ar(10)
    For p = 1 To 10
    Ar(p) = B.Offset(, p).Value
    Next p
    D(B & "") = Ar
Next
End With








Dim Ar(10)  change as Dim Ar(1 To 10)

TOP

Sub Test2()
Dim Arr, Brr, xD, i&, j%, U&
Set xD = CreateObject("Scripting.Dictionary")
Arr = Range([¤u§@ªí1!L6], [¤u§@ªí1!B65536].End(xlUp))
For i = 1 To UBound(Arr):  xD(Arr(i, 1)) = i: Next

[¤u§@ªí2!F7:O2000].ClearContents
Brr = Range([¤u§@ªí2!O7], [¤u§@ªí2!E65536].End(xlUp))
For i = 1 To UBound(Brr)
    U = xD(Brr(i, 1)): Brr(i, 2) = "¬dµL¦¹PN": If U = 0 Then GoTo 101
    For j = 2 To 11: Brr(i, j) = Arr(U, j): Next
101: Next i
[¤u§@ªí2!E7:O7].Resize(UBound(Brr)) = Brr
End Sub

TOP

¦^´_ 3# jesscc

­Y¨S¦³«Å§iOption Base 1ªº¸Ü¡A«h©³¤UªºDim Ar(10)·|¥]§tAr(0)¡BAr(1)¡BAr(2)¡BAr(3)...¡BAr(10)¦@¦³11­Ó°}¦C¤¸¯À¡C
­Y«Å§iOption Base 1ªº¸Ü¡A«hAr(10)°}¦C¥u¥]§tAr(1)¡BAr(2)¡BAr(3)...¡BAr(10)¡A10­Ó°}¦C¤¸¯À¡C
­YOption Base 1 ®³±¼ªº¸Ü¡A¾ã­Ó¸ê®Æ¶Ç¦^µ²ªG·|¦V¥k°¾¤@Äæ´N¬O³o­Ó¹D²z¡C

TOP

¦^´_ 9# Kubi
ÁÂÁÂK¤j¡A¤F¸Ñ¤F¡C
Jess

TOP

        ÀR«ä¦Û¦b : «Ý¤H°h¤@¨B¡A·R¤H¼e¤@¤o¡A´N·|¬¡±o«Ü§Ö¼Ö¡C
ªð¦^¦Cªí ¤W¤@¥DÃD