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

[µo°Ý] ½Ð±Ð¥~³¡¸ê®Æ¶×¤J®É¶¡,Åܦ¨«Dªí®æ,­n¦p¦ó«ì´_¦¨ªí®æ?

[µo°Ý] ½Ð±Ð¥~³¡¸ê®Æ¶×¤J®É¶¡,Åܦ¨«Dªí®æ,­n¦p¦ó«ì´_¦¨ªí®æ?

¦P¤@ºô¯¸¶×¤J¥~³¡¸ê®Æ®É,¥u¦³"ªÑ¥»§Î¦¨"¤Î"°ò¥»¸ê®Æ"¯àºû«ùºô¯¸¤Wªí®æ§Î¦¡,¨ä¥L³£¥H«Dªí®æÅã¥Ü,½Ð±Ð¦U¦ì°ª¤â­n¦p¦óÅý¥~³¡¸ê®Æ«ì´_ªí®æ§Î¦¡?ÁÂÁÂ
1.rar (111.35 KB)
¦~ÄÖ¤£¤p,¦ý§Ú«Ü·Q¾Ç

¦^´_ 1# bhsm


    ÁÂÁ«e½úµoªí¦¹¥DÃD»P½d¨Ò
«á¾Ç²q²q¬Ý»Ý¨D!½Ð«e½ú¸Õ¸Õ¬Ý
1_20221024_2.zip (128.29 KB)

°õ¦æ«e:


°õ¦æµ²ªG:

TOP

¥»©«³Ì«á¥Ñ Andy2483 ©ó 2022-10-25 08:09 ½s¿è

«e½ú¦­¦w!
¤µ¤Ñ¦­¤W±N¬Q¤Ñ²q´ú±¡¹Ò½m²ß°}¦Cªºµ{¦¡½X¤ß±oµù¸Ñ¤@¤U!
½Ð¦U¦ì«e½ú«ü¥¿¨Ã«ü¾É! ÁÂÁÂ!

Option Explicit
Sub TEST()
Dim Brr, Arr, Crr, c&, i&, x&, xR, R&, T, V, Y, Z
Dim B#, j, K%, P$, Q, Ra
'¡ô«Å§iÅܼÆ
Y = Array("©u²Ö­p·l¯qªí", "¸ê²£­t¶Å²ªí", "²Ö­p²{ª÷¬y¶q©uªí", "²{ª÷¬y¶q¦~ªí", "·l¯q¦~ªí")
'¡ô¥OY ¬O¤@ºû°}¦C ­Ë¤J 5­Óªíªº¦WºÙ¦r¦ê
For j = 0 To UBound(Y)
'¡ô³]¶¶¥~°j°é
   Brr = Sheets(Y(j)).Range(Sheets(Y(j)).[B3], Sheets(Y(j)).Cells(Rows.Count, 2).End(3))
   '¡ô¥OBrr°}¦C¸Ë¤JY ¦U­ÓªíªºÃöÁä¦r¦ê!
   ReDim Arr(1 To UBound(Brr), 1 To 99)
   '¡ô«Å§iArrªÅ°}¦Cªº¦C¼Æ¦PBrr!Äæ¼Æ99Äæ
   For i = 1 To UBound(Brr)
   '¡ô³]¶¶¤¤°j°é
      If Trim(Brr(i, 1)) = "" Then
      '¡ô¦pªGÃöÁä¦r¦ê¥hÀY§ÀªºªÅ¥Õ¦r¤¸«á¬OªÅ¦r¤¸?
         GoTo 111
         '¡ô±ø¥ó¦¨¥ß!´N¸õ¨ì 111 ªº¦ì¸mÄ~Äò°õ¦æ
      End If
      Crr = Split(Trim(Brr(i, 1)), " ")
      '¡ô±NÃöÁä¦r¦ê¥hÀY§ÀªºªÅ¥Õ¦r¤¸«á,¥HªÅ¥Õ¦r¤¸¤À³Î¦r¦ê¦¨¬°Crr¤@ºû°}¦C
      For x = 0 To UBound(Crr)
      '¡ô³]¶¶¤º°j°é!±q0¨ìCrr°}¦Cªº«á¤@­Ó
         Arr(i, x + 1) = Crr(x)
         '¡ô±NCrr¤@ºû°}¦C±a¤JArrªº¤Gºû°}¦C!
      Next
   
111
   Next
   Sheets(Y(j)).[C3].Resize(UBound(Arr), 99) = Arr
   '¡ô±N¦U­Óªí±q[C3]¶}©l¶K¤Jµ²ªG¸ê®Æ
Next
Set Arr = Nothing
Set Brr = Nothing
Set Crr = Nothing
End Sub

TOP

¦^´_ 3# Andy2483
·PÁ±zªº«ü¾É,¥i¥H¨Ï¥Î¤F,«áÄò­Y¦³¨ä¥L°ÝÃD½Ð±Ð,©|½Ð¤£§[«ü±Ð
¦~ÄÖ¤£¤p,¦ý§Ú«Ü·Q¾Ç

TOP

¦^´_ 3# Andy2483
½Ð±ÐAndy2483¤j:
±z±Ð¾Éªº³o­ÓVBA­n¦p¦ó¦b¤u§@ªí¤¤¨Ï¥Î"Call"©I¥s¥¦©O?§Ú¦b¤u§@ªí1¤¤¥Î"Call"©I¥s¥¦«oµLªk¦Û°Ê°õ¦æ,½Ð°Ý­n¦p¦ó¯à¥Î"Call"©I¥s"TEST"VBA©O?ÁÂÁ T-1.rar (130.68 KB)
¦~ÄÖ¤£¤p,¦ý§Ú«Ü·Q¾Ç

TOP

¥»©«³Ì«á¥Ñ Andy2483 ©ó 2023-2-23 13:01 ½s¿è

¦^´_ 5# bhsm


    ÁÂÁ«e½ú¦A¦^´_
¥H¤Uµ{¦¡½X½Ð¸Õ¸Õ¬Ý

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$A$1" Then
'¡ô¦pªGIJµo®æ¦ì§}¬O[A1]
   Range("B1").Select
   '¡ôÀx¦s®æ´å¼Ð¿ï¨ú[B1]Àx¦s®æ
   Call TEST
   '¡ô°õ¦æ°Æµ{¦¡(TEST)
End If
End Sub
===============================
[B1]¦³¤½¦¡,©Ò¥H«á¾Ç²q´ú«e½ú¬O­n¦b[A1]¿é¤J«áIJµo>°õ¦æ°Æµ{¦¡
¥Î¦æ°Ê¸Ë¸mÂsÄý½×¾Â¾Ç²ß«Ü¤è«K,ÁÂÁ½׾¸gÀç¹Î¶¤
½Ð¤j®a¤@°_¤W½×¾Â¨Ó¥æ¬y

TOP

¦^´_ 6# Andy2483
ª½±µ§ï¥Î±zªº«Øij,ÁÙ¬OµLªkCall TEST­C!
¦~ÄÖ¤£¤p,¦ý§Ú«Ü·Q¾Ç

TOP

¦^´_ 7# bhsm

¥i¯à¬OEXCELIJµo¥¢®Ä¤F,°õ¦æ¥H¤U Sub «ì´_IJµo() ÅýIJµo«ì´_«á,[A1]¦A¿é¤J,¸Õ¸Õ¬Ý
Sub «ì´_IJµo()
Application.EnableEvents = True
End Sub
¥Î¦æ°Ê¸Ë¸mÂsÄý½×¾Â¾Ç²ß«Ü¤è«K,ÁÂÁ½׾¸gÀç¹Î¶¤
½Ð¤j®a¤@°_¤W½×¾Â¨Ó¥æ¬y

TOP

¦^´_ 8# Andy2483
¤p§Ì¤£¤~,½Ð°Ý¾ã­ÓVBA­n¦p¦ó¼g,ÁÂÁÂ
¦~ÄÖ¤£¤p,¦ý§Ú«Ü·Q¾Ç

TOP

¦^´_ 9# bhsm


    ½Ð«e½ú¤U¸ü¸Õ¸Õ¬Ý

T-1_20230223_1.zip (125.95 KB)
¥Î¦æ°Ê¸Ë¸mÂsÄý½×¾Â¾Ç²ß«Ü¤è«K,ÁÂÁ½׾¸gÀç¹Î¶¤
½Ð¤j®a¤@°_¤W½×¾Â¨Ó¥æ¬y

TOP

        ÀR«ä¦Û¦b : ¤f»¡¤@¥y¦n¸Ü¡A¦p¤f¥X½¬ªá¡F¤f»¡¤@¥yÃa¸Ü¦p¤f¦R¬r³D¡C
ªð¦^¦Cªí ¤W¤@¥DÃD