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

[µo°Ý] ¦p¦ó½Æ»s¤u§@ªí¤@ªº©Ò¦³¸ê®Æ¦Üªí¤Gªº¤£¦PÄæ¦ì

[µo°Ý] ¦p¦ó½Æ»s¤u§@ªí¤@ªº©Ò¦³¸ê®Æ¦Üªí¤Gªº¤£¦PÄæ¦ì

¦U¦ì¥ý¶i¦n..
  ¥H¤U¦³­ÓÀÉ,¥Î¨ç¼Æªº¸Ü,¬O¥i¥H¸Ñ¨M¦¹°ÝÃD,¦ý¦]¸ê®ÆÃe¤j,­pºâ¸ûºC,¦Ó·Q¥Îvba¸Ñ¨M
  1.ªí¤@ªº¸ê®Æ¦C¦ì¬O©T©wªº
  2.ªí¤Gªº¦C¦ì,·|¦]¼t°Ó©¿µM§ïÅܦC¦ì,±Æ¦C¶¶§Ç·|¤£¦P
  3.§Æ±æ¯à¦bªí¤GÅܰʦC¦ì®É..¯à§âªí¤@ªº¸ê®Æ½Æ»s¨ìªí¤G
  ÁÂÁ¦U¦ì¥ý¶i
test1.rar (13.8 KB)

¦^´_ 1# hungrn

ªí1,ªí2 ¥Î¹q«×¼Æ(«×)  »Ý§ï¦¨  ¥Î¹q«×¼Æ
ÁÙ¦³¨ä¥L¼gªk,¤]½Ð¤j®aµoªí
  1. Option Explicit
  2. Sub Ex()
  3.     Dim i As Variant
  4.     For Each i In Names
  5.         i.Delete
  6.     Next
  7.     Worksheets("¤u§@1").UsedRange.CreateNames True, False
  8.     'Range.CreateNames ¤èªk¦b«ü©w½d³ò¤¤¨Ì¾Ú¤u§@ªí¤¤ªº¤å¦r¼ÐÅҫإߦWºÙ
  9.     With Worksheets("¤u§@2").UsedRange
  10.         For i = 1 To .Columns.Count
  11.             If .Cells(1, i) <> "" Then
  12.                 With Range(.Cells(1, i).Value)   '¤u§@ªí¤¤ªº¤å¦r¼ÐÅҫإߦWºÙ
  13.                     Cells(2, i).Resize(.Rows.Count) = .Value
  14.                 End With
  15.             End If
  16.         Next
  17.     End With
  18. End Sub
½Æ»s¥N½X
·P®¦ªº¤ß......(¦b³Â»¶®a±Ú°Q½×°Ï.¥Î¤ß¾Ç²ß·|¦³¶i¨Bªº)
¦ý¸ê·½µL­­,«á´©¦³­­,  ¤@¤Ñ1¤¸ªºÃÙ§U,¤H¤H¦³¯à¤O.

TOP

¥»©«³Ì«á¥Ñ hungrn ©ó 2017-8-4 18:11 ½s¿è

ª©¥D¦n
Cells(2, i).Resize(.Rows.Count) = .Value
§Ú§ï¦¨sheets("¤u§@2").Cells(2, i).Resize(.Rows.Count) = .Value
´N¥i¥¿½T°õ¦æ¤F..ÁÂÁÂ
½Ð°Ý¬O§_¦³¤£¶·¥[¤J¦WºÙªº¼gªk..?

TOP

Sub TEST()
Dim MH, C%
With Sheets("¤u§@1").UsedRange
¡@¡@For C = 1 To .Columns.Count
¡@¡@¡@¡@MH = Application.Match(.Item(C), [¤u§@2!1:1], 0)
¡@¡@¡@¡@If Not IsError(MH) Then
¡@¡@¡@¡@¡@¡@.Columns(C).Offset(1, 0).Copy [¤u§@2!A1].Cells(2, MH)
¡@¡@¡@¡@End If
¡@¡@Next C
End With
End Sub

TOP

¦^´_ 3# hungrn
  1. Sub ex()
  2. Dim r&, A As Range, d As Object
  3. Set d = CreateObject("Scripting.Dictionary")
  4. With Sheets(1)
  5. r = .UsedRange.Rows.Count - 1
  6. For Each A In .UsedRange.Rows(1).Cells 'Ū¨ú¤u§@ªí1¸ê®Æ
  7.    d(A.Value) = A.Offset(1).Resize(r, 1).Value
  8. Next
  9. With Sheets(2)
  10. .UsedRange.Offset(1).ClearContents
  11. For Each A In .UsedRange.Rows(1).Cells '¼g¤J¨ì¤u§@ªí2
  12. If d.exists(A.Value) Then A.Offset(1).Resize(r, 1) = d(A.Value)
  13. Next
  14. End With
  15. End With
  16. End Sub
½Æ»s¥N½X
¾Ç®üµL²P_¤£®¢¤U°Ý

TOP

ÁÂÁÂ3¦ìª©¥Dªº«ü¾É...
CreateObject("Scripting.Dictionary")
¥i°Ñ¦Ò http://gb.twbts.com/index.php?topic=510.0
³o½g

TOP

½Ð°Ý...¦pªG¤£¬O¥þ³¡¨Ï¥Î½d³òUsedRange..
¦Ó¬O¬Y¤@­Ó¿ï¨ú½d³ò¦prange("a5:d50") ¸Ó¦p¦ó§ó§ï
¦]¬°§Ú¥Î©w¸q½d³ò¤£¦æ

³Â·Ð¦U¦ì¤F

TOP

©êºp¦U¦ì¤F...¬O§Ú½d³ò©w¸q¿ù»~
¤w¸g¸Ñ¨M¤F...ÁÂÁ¦U¦ì

TOP

        ÀR«ä¦Û¦b : ¡i¦æµ½­n¤Î®É¡j¦æµ½­n¤Î®É¡A¥\¼w­n«ùÄò¡C¦p¿N¶}¤ô¤@¯ë¡A¥¼¿N¶}¤§«e¤d¸U¤£­n°±º¶¤õ­Ô¡A§_«h­«¨Ó´N¤Ó¶O¨Æ¤F¡C
ªð¦^¦Cªí ¤W¤@¥DÃD