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

[µo°Ý] ±µÄò°O¿ý¸ê®Æ

¦^´_ 10# PJChen
  1. With Sh.Range("aw1").CurrentRegion
  2.         Set Rng = Range(.Rows(2), .Rows(.Rows.Count))
  3.     End With
½Æ»s¥N½X
·P®¦ªº¤ß......(¦b³Â»¶®a±Ú°Q½×°Ï.¥Î¤ß¾Ç²ß·|¦³¶i¨Bªº)
¦ý¸ê·½µL­­,«á´©¦³­­,  ¤@¤Ñ1¤¸ªºÃÙ§U,¤H¤H¦³¯à¤O.

TOP

¦^´_ 11# GBKEE
G¤j,
VBA¬O©ñ¦b¥t¤@­Óµ{¦¡¤¤,°õ¦æ®Éµ{¦¡¥d¦b¬õ¦â¦r¨º¬q,»¡¬O«¬ºA¤£²Å,½ÐÀ°¦£¬Ý¤U°ÝÃD,·PÁ¡I Text°õ¦æ.rar (30.12 KB)
  1. Sub ¼t¯ÊText()
  2. Dim Rng As Range, M As Variant, c As Range, r As Range, W As Workbook, Sh As Worksheet

  3.     Set W = Workbooks("³Ì·s®w¦s.xlsx")
  4.     Set Sh = W.Sheets("¼t¯Êªí")
  5.     With Sh.Range("aw1").CurrentRegion
  6.         Set Rng = Range(.Rows(2), .Rows(.Rows.Count))  '¨Ó·½°Ï°ì ¤£Åª¨ú²Ä1¦C,³]©w±q²Ä2¦C¶}©lŪ¨ú
  7.         M = "¼t¯ÊText"   '³]©wM¬°±µ¦¬¸ê®ÆªºÅܼÆ,ªíÀY¬°""¤ºªº¤å¦r,¥i¥ô·N§ó§ï
  8.         For Each c In Rng.Columns   'Columns ½d³òªºColumn¶°¦Xª«¥ó
  9.             For Each r In c.Cells   'Cells ½d³òªºCell¶°¦Xª«¥ó
  10. [color=Red]                If r = "" Or r = 0 Then Exit For[/color]   '¹JªÅ®æor 0µ{¦¡«K¸õ¤U¤@Äæ,ª½¨ì¸ê®Æµ²§ô
  11.                 M = M & "," & r
  12.             Next
  13.         Next
  14.         M = Split(M, ",")
  15.     End With
  16.         With Sh.Range("bm1")  '±NŪ¨ú¸ê®Æ,Åã¥Ü¨ìBMÄæ
  17.             .CurrentRegion.Clear
  18.             .Resize(UBound(M) + 1) = Application.WorksheetFunction.Transpose(M)
  19.         End With
  20. End Sub
½Æ»s¥N½X

TOP

¦^´_ 12# PJChen
  1. Option Explicit
  2. Sub ¼t¯ÊText()
  3. Dim Rng As Range, M As Variant, W As Workbook, Sh As Worksheet, i As Integer
  4.     Set W = Workbooks("³Ì·s®w¦s.xlsx")
  5.     Set Sh = W.Sheets("¼t¯Êªí")
  6.   '** §AÀɮתº¸ê®Æ¤£¾A¥Î 'Range.CurrentRegion ÄÝ©Ê ¶Ç¦^ Range ª«¥ó¡A¸Óª«¥ó¥Nªí¥Ø«eªº°Ï°ì¡C
  7.   ** '¥Ø«e°Ï°ì¬O«ü¥H¥ô·NªÅ¥Õ¦C¤ÎªÅ¥ÕÄ檺²Õ¦X¬°Ãä¬Éªº½d³ò

  8.     With Sh.Range("aw1").CurrentRegion
  9.         Set Rng = Range(.Rows(2), .Rows(.Rows.Count))  '¨Ó·½°Ï°ì ¤£Åª¨ú²Ä1¦C,³]©w±q²Ä2¦C¶}©lŪ¨ú
  10.         Rng.Cells(1, 5).Select
  11.         MsgBox "§AÀɮפ£¾A¥Î CurrentRegionRng" & vbLf & Rng.Address(, , , 1, 1) & vbLf & Selection.Address(0, 0) & "¿ù»~­È ³y¦¨µ{¿ù»~"
  12.     End With
  13.     M = "¼t¯ÊText"   '³]©wM¬°±µ¦¬¸ê®ÆªºÅܼÆ,ªíÀY¬°""¤ºªº¤å¦r,¥i¥ô·N§ó§ï
  14.     Set Rng = Sh.Range("aw2")
  15.     Do Until Rng.Text = "" Or Rng.Text = 0 Or IsError(Rng)
  16.         i = 1
  17.         Do While Rng(i).Text <> "" And Rng(i).Text <> 0 And Not IsError(Rng(i))
  18.             M = M & "," & Rng(i)   '²Äi­Ó³B¦s®æ
  19.             i = i + 1
  20.         Loop
  21.         Set Rng = Rng.Offset(, 1)  '**¥ª²¾¤@Äæ
  22.     Loop
  23.     M = Split(M, ",")
  24.     With Sh.Range("bm:bm")  '±NŪ¨ú¸ê®Æ,Åã¥Ü¨ìBMÄæ
  25.         .Clear
  26.         .Resize(UBound(M) + 1) = Application.WorksheetFunction.Transpose(M)
  27.     End With
  28. End Sub
½Æ»s¥N½X
·P®¦ªº¤ß......(¦b³Â»¶®a±Ú°Q½×°Ï.¥Î¤ß¾Ç²ß·|¦³¶i¨Bªº)
¦ý¸ê·½µL­­,«á´©¦³­­,  ¤@¤Ñ1¤¸ªºÃÙ§U,¤H¤H¦³¯à¤O.

TOP

¦^´_ 13# GBKEE
Dear G¤j,
°õ¦æ¹Lµ{°±¦b³o¸Ì,½Ð°Ý¸Ó¦p¦ó§ï?
Rng.Cells(1, 5).Select

20190919.205801.jpg
2019-9-19 21:00

TOP

¦^´_ 14# PJChen
©ú¥Õ¦p¦ó¿ù»~«á³o¬qµ{¦¡½X§R°£
  1. With Sh.Range("aw1").CurrentRegion
  2.               Set Rng = Range(.Rows(2), .Rows(.Rows.Count))  '¨Ó·½°Ï°ì ¤£Åª¨ú²Ä1¦C,³]©w±q²Ä2¦C¶}©lŪ¨ú
  3.                Rng.Cells(1, 5).Select    '********±Nµøµ¡²¾¨ì  [ ³Ì·s®w¦s.xlsx]   ¦A°õ¦æµ{¦¡¸Õ¸Õ¬Ý******
  4.                MsgBox "§AÀɮפ£¾A¥Î CurrentRegionRng" & vbLf & Rng.Address(, , , 1, 1) & vbLf & Selection.Address(0, 0) & "¿ù»~­È ³y¦¨µ{¿ù»~"
  5.                End With
½Æ»s¥N½X
¬O­n§A
·P®¦ªº¤ß......(¦b³Â»¶®a±Ú°Q½×°Ï.¥Î¤ß¾Ç²ß·|¦³¶i¨Bªº)
¦ý¸ê·½µL­­,«á´©¦³­­,  ¤@¤Ñ1¤¸ªºÃÙ§U,¤H¤H¦³¯à¤O.

TOP

¦^´_ 15# GBKEE
·PÁÂG¤j,

°õ¦æ¨S°ÝÃD¤F^.^

TOP

¦^´_ 6# GBKEE

½Ð°ÝG¤j,

§Ú¦³¥t¤@ªí®æ»Ý­nÃþ¦ü³o¼ËªºÂà¶K,¦ý¤£­n¶K¦¨¦P¤@Äæ,¦Ó¬O¨Ì­ì¸ê®ÆªºÄæ¼Æ¥hÂà¶K,¦ý¸ê®Æ¹J0©Î¦UºØ¿ù»~­È #N/A!¡B#VALUE!¡B#DIV/O!³£²¤¹L¤£¨ú(¦p¦P¶K¹Ï)
ª½¨ì"ªÅ¥Õ",«h¥Nªí¾ãÄæ¸ê®Æµ²§ô
¥»¨Ó·Q¸ÕµÛ¦Û¦æ­×§ï,¦ý¸Õ¤F«Ü¤[³£§ï¤£¥X¨Ó,,½Ð°Ý§Ú­n¦p¦ó­×§ïµ{¦¡?
  1. Option Explicit
  2. Sub EX()
  3.     Dim Rng As Range, M As Variant, c As Range, r As Range
  4.     Set Rng = Sheets("KÄæŪ¨ú¸ê®Æ").Range("a1").CurrentRegion
  5.     M = "Åã¥Üµ²ªG"
  6.     For Each c In Rng.Columns   'Columns ½d³òªºColumn¶°¦Xª«¥ó
  7.         For Each r In c.Cells   'Cells ½d³òªºCell¶°¦Xª«¥ó
  8.             If r = "" Or r = 0 Then Exit For
  9.             M = M & "," & r
  10.         Next
  11.     Next
  12.     M = Split(M, ",")
  13.     With Sheets("Åã¥Üµ²ªG").Range("a1")
  14.         .CurrentRegion.Clear
  15.         .Resize(UBound(M) + 1) = Application.WorksheetFunction.Transpose(M)
  16.     End With
  17. E
½Æ»s¥N½X
20191005.192923.jpg
2019-10-5 19:33
  
KÄæ±µÄò°O¿ý_©µ¦ù.rar (23.32 KB)

TOP

        ÀR«ä¦Û¦b : §g¤l¬°¥Ø¼Ð¡A¤p¤H¬°¥Øªº¡C
ªð¦^¦Cªí ¤W¤@¥DÃD