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

[µo°Ý] EXCEL VBA ªº°ÝÃD

[µo°Ý] EXCEL VBA ªº°ÝÃD

½Ð°ÝªþªíªºÀɮפ¤¡A¦p¦ó¦b¤u§@ªí1¤¤ªí®æ¡A¨Ï¥ÎVBA§ä¥X¥Ò¡B¤A¡B¤þªº³Ì«á¤@¦C¼Æ­È©M¤é´Á¼g¨ì¤u§@ªí2¤¤¡A½Ð¥s¦³¸ô¹Lªº¤j­ô¥i¥H«ü¾É¤@¤U¡AÁÂÁÂ

¥t¦A½Ð±Ð¡A°²¦p¥Ò¤A¤þ¤B(Äæ¦ì)¦³«Ü¦h­Ó¡A¨ºVBAªº»yªk¤S¸Ó¦p¦ó½Õ¾ã©O

tt.rar (7.22 KB)

TT

¦^´_ 1# idnoidno
  1. Sub ¥¨¶°1()
  2. n = 1
  3. For Each Rng In ¤u§@ªí1.Range("B1", ¤u§@ªí1.Cells(1, Columns.Count).End(xlToLeft).Address) '²Ä¤@¦C"°ÊºA"Ū¨ú°µ´`Àô
  4.     RngRow = ¤u§@ªí1.Columns(Rng.Column).Find("*", , , , , xlPrevious).Row '´M§ä§ïÄ檺³Ì«á¤@­ÓÀx¦s®æ
  5.     ¤u§@ªí2.Cells(n, 1) = Cells(RngRow, 1) '¤é´Á¼g¤J¤u§@ªí2
  6.     ¤u§@ªí2.Cells(n, 2) = Cells(RngRow, Rng.Column) '³Ì«áªº¼Æ­È¼g¤J¤u§@ªí2
  7.     n = n + 1
  8. Next
  9. End Sub
½Æ»s¥N½X

TOP

·PÁ¤j¤jªº¦^´_¡A§Ú¨Ó¬ã¨s¸Õ¸Õ¬Ý¡A¤£À´ªº¡A§Æ±æ±z¥i¥H±µ¨ü§Úªº½Ð¯q
ÁÂÁÂ

TOP

½Ð°Ýªülpk187¤j¤j
°õ¦æªºµ²ªG¦³ÂI¤£¤@¼Ë
¥t¥~loop¦n¹³¤Ó¦h¦¸

TOP

¦^´_ 4# idnoidno

§Ú¦A°õ¦æ¤@¦¸¡Aµ²ªG¬O§A­nªº·N«ä¡A¤£¹L§ÚÁÙ¬O­×§ï¤@¤U
  1. Sub ¥¨¶°1()
  2. n = 1
  3. For Each Rng In ¤u§@ªí1.Range("B1", ¤u§@ªí1.Cells(1, Columns.Count).End(xlToLeft).Address) '²Ä¤@¦C"°ÊºA"Ū¨ú°µ´`Àô
  4.     RngRow = ¤u§@ªí1.Columns(Rng.Column).Find("*", , , , , xlPrevious).Row '´M§ä§ïÄ檺³Ì«á¤@­ÓÀx¦s®æ
  5.     ¤u§@ªí2.Cells(n, 1) = Rng
  6.     ¤u§@ªí2.Cells(n, 2) = ¤u§@ªí1.Cells(RngRow, 1) '¤é´Á¼g¤J¤u§@ªí2
  7.     ¤u§@ªí2.Cells(n, 3) = ¤u§@ªí1.Cells(RngRow, Rng.Column) '³Ì«áªº¼Æ­È¼g¤J¤u§@ªí2
  8.     n = n + 1
  9. Next
  10. End Sub
½Æ»s¥N½X
¤G¡B§Úµ{§Ç¬O°µ°ÊºA´`Àô¡A¥H§A¤W¶ÇªºÀÉ®×¥h°µªº¡A¨Ì³o­ÓÀÉ®×¥u°µ¤F"¥Ò¤A¤þ"3¦¸´`Àô¡A©Ò¥H§A»¡ªº¤Ó¦hLoop¬O«ü3¦¸¤Ó¦h¶Ü¡H

TOP

¦^´_ 4# idnoidno

°õ¦æµ²ªG¦p¤U2¹Ï
   

TOP

¤j¤j¡AÁÂÁ¡A¥i¥H©M§A½u¤W½Ð¯q¨Ì¤U¶Ü

TOP

¦^´_ 6# lpk187
  1. Sub ex()
  2. Dim Ar()
  3. Dim A As Range
  4. With ¤u§@ªí1
  5. For Each A In .Range(.[B1], .[B1].End(xlToRight))
  6.    c = Application.Lookup(9.9E+307, A.EntireColumn)
  7.    d = Application.Lookup(9.9E+307, A.EntireColumn, .Columns("A"))
  8.    ReDim Preserve Ar(s)
  9.    Ar(s) = Array(A.Value, CDate(d), c)
  10.    s = s + 1
  11. Next
  12. With ¤u§@ªí2
  13.    .UsedRange.ClearContents
  14.    .[A1].Resize(s, 3) = Application.Transpose(Application.Transpose(Ar))
  15. End With
  16. End With
  17. End Sub
½Æ»s¥N½X
¾Ç®üµL²P_¤£®¢¤U°Ý

TOP

¦^´_ 7# idnoidno


    ­n¦p¦ó½u¤W¡A§Ú¤£·|­C¡I

TOP

«zÁÂÁª©¥D¥X¤â¦^À³
¤p§Ì¤~¾Ç²Ê²L¡A¥i¯à­n¦n¦nªº¬ãŪ¤F
ÁÙ¬O¤Q¤À·PÁª©¥D

TOP

        ÀR«ä¦Û¦b : ¥Í®ð¡A´N¬O®³§O¤Hªº¹L¿ù¨ÓÃg»@¦Û¤v¡C
ªð¦^¦Cªí ¤W¤@¥DÃD