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

[µo°Ý] ¦p¦ó¾î¦V¶ñ¤J¸ê®Æ

[µo°Ý] ¦p¦ó¾î¦V¶ñ¤J¸ê®Æ

¥ÎSheets("E")¥h¤ñ¹ïSheets("DATA")¡A¦b"DATA"§ì¥X¬ÛÃö¸ê®Æ¡A¾î¦V¶ñ¤JSheets("E")¹ïÀ³¦ì¸m¡C±q¨Ó¨S³o¼Ë³B²z¹L¸ê®Æ¡A¤£ª¾¸Ó¦p¦ó°µ?
E_TEST.rar (25.81 KB)
Jess

¦^´_ 1# jesscc


    ¬O³o¼Ë¶Ü?(¦¹¬°·sª©RARµLªk¸ÑÀ£ÁY½Ð§ó·sWin RAR 5.60)
  1. Private Sub CommandButton1_Click()
  2. 'Module1.Ext
  3. Dim f1, f2, f3 As Workbook '«Å§i¬¡­¶Ã¯ÅܼÆ
  4. Set f1 = Sheets("DATA") '·s¼W¬¡­¶Ã¯¦WºÙ
  5. Set f2 = Sheets("E") '·s¼W¬¡­¶Ã¯¦WºÙ
  6. f2.[F5:Z1000] = "" '²M°£½d³ò¸ê®Æ¡A½T«O¨C¦¸°õ¦æ¸ê®Æ§ó·s
  7. For Each aa In f2.Range([D5], [D5].End(xlDown)) 'D5¶}©l¦V¤U°j°éŪ¨ú
  8. X = 0 '¼Æ­È
  9.     For i = 5 To Application.CountA(f1.Range("E:E")) + 3 '°j°é¼Æ­È(°_©l¦ì¸mÃö«Y©Ò¥HÁ`¼Æ+3)
  10.     If aa.Offset(, 0) = f1.Cells(i, 5) Then '¤ñ¹ï¦WºÙ¬Û²Å°õ¦æ
  11.         If aa.Offset(, 8 + X) = "" Then '¤ñ¹ï¸Óaa³B©¹¥k²Ä8®æ¬O§_ªÅ¥Õ
  12.             aa.Offset(, 8 + X) = f1.Cells(i, 2) 'ªÅ¥Õ¶ñ¤J"DATA"PNªº­È
  13.             aa.Offset(, 9 + X) = f1.Cells(i, 1) 'ªÅ¥Õ¶ñ¤J"DATA"VENªº­È
  14.         Else '¤£¬OªÅ¥Õ©¹¥k2­Ó¡A°j°éi-1­«ÂФ@¦¸°j°é¡Aª½¨ì¶ñ¤J¬°¤î
  15.             X = X + 2
  16.             i = i - 1
  17.         End If
  18.     End If
  19.     Next i
  20. Next aa
  21. End Sub
½Æ»s¥N½X

E_TEST.rar (27.33 KB)

¤@¤À§V¤O¡A¤@¤À¦¬Ã¬¡C
µo°ÝÃD«e¥i¥H¥ý·j¯Á¤º¤å¬O§_¦³¬ÛÃö½d¨Ò¡C

TOP

¦^´_ 2# faye59
¥¿¬O§Ú­nªºµ²ªG¡AÁÂÁ¤j¤j¡C
Jess

TOP

¦^´_ 2# faye59
f¤j¡A§Ú¦³­Ó¤p°ÝÃD
§Ú§âApplication.CountA(f1.Range("E:E")) + 3
§ï¦¨Cells(5000, 5).End(xlUp).Row
¶Ç¦^ªº¼Æ­È¬O¤@¼Ëªº¡A¦ý¬°¤°»ò°õ¦æ·|¤£¥¿±`?
Jess

TOP

¦^´_ 4# jesscc


    ¦]¬°"¬¡­¶Ã¯"
¥i¥H¦bFor i «e¥[¤Jf1.Select¡A
©Î¬Of1.Cells(5000, 5).End(xlUp).Row¡A
¤£¹L§Ú¼gªº¤£¤Ó¾A¦X¥ÎSelect¡C

§Ú¦b«e­±¤w¸g¥ýSet f1¤Îf2¬¡­¶Ã¯¡A
©Ò¥H´N¨S¦³f1.Selectªº°Ê§@¡C

Ãö©óExcel¸ÕºâªíÀx¦s®æ¤½¦¡³oÂI§Ú»¡©ú¤@¤U:
Workbook(¤u§@ªí).Sheets(¬¡­¶Ã¯).Range or Cells or Offset(Àx¦s®æ¦ì¸m)
§A§i¶Dµ{¦¡°õ¦æ¦ì¸m¦]¸Ó¦p¦¹¡A
¦ý¬°¤°»ò¥u­n¥´Cells¤@¼Ë¯à°µ°Ê©O?
¦]¬°³o¬OExcel VBA¡A
µ{§Ç¦b°õ¦æ®É§PŪªº¬O·í«e¬¡­¶Ã¯¡A
¦pªG¥H«á§A¦³¼g¨ì¨ä¥¦µ{¦¡»y¨¥´N·|À³¥Î±o¨ì¦¹¤½¦¡¡C

¥ÎVB.netÁ|­Ó¨Ò¤l
VB.net(¥Ò­û¶l®t);Excel(¤A­û¦í¤á)
¥Ò­û¤µ¤Ñ°e«H¨ì¤A­û®a¡A
¦a§}¥u¼g520¸¹¡A
¨ì¹F¥Øªº«áÀH§Y§ë¤J520¸¹1¼Ó¡A
¦ý¨ä¹ê¤A­û®a¦í2¼Ó¡A
³o®É¬O¤£¬O´N°e¿ù«H¤F¡C

¤@¼ËÅÞ¿è¡A
§â¦ì¸m¼g¸Ô²Ó¤ñ¸û¦n¡A
¦ý¬O¥Ø«e¶È°õ¦æVBA³¡¤À¡A
©Ò¥H¦³¨Ç¦a¤è§Ú­Ì¬O¥i¥H¬Ù²¤ªº¡A
¦ý¦b¨ä¥¦Sheet¸ê®ÆÂà´«®É´N¥²¶·±Ô­z²M·¡©Î¬O¤Þ¥Î¶µ¥Ø¡C
¤@¤À§V¤O¡A¤@¤À¦¬Ã¬¡C
µo°ÝÃD«e¥i¥H¥ý·j¯Á¤º¤å¬O§_¦³¬ÛÃö½d¨Ò¡C

TOP

¦^´_ 4# jesscc

Cells(5000, 5).End(xlUp).Row
§A¬Ý¬Ý·í«e¬¡­¶Ã¯ªº­È¬O¦h¤Ö~¨S¿ùªº¸Ü¬O7
´N¬OSheets("E")ªºEÄæ¦ì0.896¡B0.896¡B6.3385³o¸Ì
¦pªG¥B´«¨ì"DATA"ªº¸ÜEÄæ¦ìÀ³¸Ó­È¬°12
¤@¤À§V¤O¡A¤@¤À¦¬Ã¬¡C
µo°ÝÃD«e¥i¥H¥ý·j¯Á¤º¤å¬O§_¦³¬ÛÃö½d¨Ò¡C

TOP

¦^´_ 1# jesscc


¦b"E" ¤u§@ªí °õ¦æ¥H¤U¥¨¶°(²³æ¼gªk)

Sub Ext()
For R = 5 To [D4].End(xlDown).Row
  Cells(R, 12) = Sheets("DATA").Range("E:E").Find(Cells(R, 4), LookAt:=xlWhole).Offset(, -3).Value
  Cells(R, 13) = Sheets("DATA").Range("E:E").Find(Cells(R, 4), LookAt:=xlWhole).Offset(, -4).Value
Next R
End Sub
µ{¦¡¬O¨Ì»Ý¨D¼gªº¡A»Ý¨Dªí¹F¤£²M·¡
©ÎªÌ¨S¦³¤W¶Çªþ¥ó¡A·R²ö¯à§U

TOP

Sub Ext()
Dim xR As Range, d, R&, C&
Range([E!A1], Sheets("E").UsedRange).Offset(4, 11).ClearContents
Set d = CreateObject("Scripting.Dictionary")
For Each xR In Range([E!D5], [E!D65536].End(xlUp))
    If xR <> "" Then d(xR & "") = xR.Row
Next
For Each xR In Range([DATA!E5], [DATA!E65536].End(xlUp))
    R = d(xR & ""):   If R = 0 Then GoTo 101
    d(xR & "//") = d(xR & "//") + 2:  C = d(xR & "//")
    [E!L1].Cells(R, C - 1).Resize(1, 2) = Array(xR(1, -2), xR(1, -3))
101: Next
End Sub

TOP

¦^´_ 8# ­ã´£³¡ªL
·Ç¤jªº»yªk¥i¥H¥Î¦bvlookup¤Ï¦V¬d¸ß,¥B¬O¾A¥Î¦h­Ó¬Û¦P±ø¥ó¥X²{ªºª¬ªp¤U,¤]¬O¤@ª½¦b´M§äªº§Þªk,¨ü±Ð¤F.

TOP

        ÀR«ä¦Û¦b : ¡i¬O§_µo´§¤F¨}¯à¡H¡j¤H¶¡¹Ø©R¦]¬°µu¼È¡A¤~§óÅã±o¬Ã¶Q¡CÃø±o¨Ó¤@½ë¤H¶¡¡AÀ³°Ý¬O§_¬°¤H¶¡µo´§¤F¦Û¤vªº¨}¯à¡A¦Ó¤£­n¤@¨ý¨Dªø¹Ø¡C
ªð¦^¦Cªí ¤W¤@¥DÃD