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

[µo°Ý] ¨Ï¥ÎVBA¦Û­q½d³ò¸ê®ÆÅçÃÒ¥¢±Ñ

[µo°Ý] ¨Ï¥ÎVBA¦Û­q½d³ò¸ê®ÆÅçÃÒ¥¢±Ñ

·Q½Ð±Ð¤j¤j­Ì¬°¦ó·|¥¢±Ñ©O~?
'¦Û­q°Ï°ì
With Sheets("Âà´«ªí")
        myRange = .Range("s2:s" & .Range("s65536").End(xlUp).Row) '¸ê®ÆÅçÃÒ¥Î
end With

'¸ê®ÆÅçÃÒ¿ï³æ
'¸Õ¹L¨âºØ¤è¦¡
'²Ä¤@ºØ
    For Each e In myRange
        arr = arr & "," & e
    Next
    arr = Split(Mid(arr, 2, 10000), ",")
    With Range("d2:d" & allr).Validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
        xlBetween, Formula1:=arr
    End With
'²Ä¤GºØ¬Oª½±µ±NArr´«¦¨myRange
PKKO

·Q½Ð±Ð¤j¤j­Ì¬°¦ó·|¥¢±Ñ©O~?
'¦Û­q°Ï°ì
With Sheets("Âà´«ªí")
        myRange = .Range("s2:s" & .Ran ...
PKKO µoªí©ó 2015-1-2 18:21



    °ª¤â¤j¤j­Ì~¨D§U¨D§U~
PKKO

TOP

¦^´_ 2# PKKO


    §ï³o¼Ë¸Õ¸Õ
  1. Dim myrange As Range
  2. With Sheets("Âà´«ªí")
  3.      Set myrange = .Range("s2:s" & .Range("s65536").End(xlUp).Row) '¸ê®ÆÅçÃÒ¥Î
  4. End With
½Æ»s¥N½X

TOP

§Ú§ï¦¨¥H¤U,¤´µLªk¨Ï¥Î
  1. With Sheets("Âà´«ªí")
  2.         cc = .Rows(1).Find("©Ò¦³³æ¦ì", Lookat:=xlWhole).Column '¥N¸¹ªºColumn
  3.         Set myRange = .Range("T2:T" & .Range("T65536").End(xlUp).Row) '¸ê®ÆÅçÃÒ¥Î
  4.         For Each e In myRange
  5.             ARR = ARR & "," & e
  6.         Next
  7.         ARR = Split(Mid(ARR, 2, 10000), ",")'¬Ý¹L°}¦Cªº­È½T©w¨S°ÝÃD
  8.         With Range("d2:d" & allr).Validation
  9.             .Delete
  10.             .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
  11.             xlBetween, Formula1:=ARR'¥d¦b³oÃä
  12.         End With
  13. End With
½Æ»s¥N½X
PKKO

TOP

½Ð°Ý¸ê®ÆÅçÃÒªº³¡¤À¦³¤j¤j¯à´£¨Ñ¦nªº¸Ñ¨M¤è®×¶Ü?
ÁÙ¦³¸ê®ÆÅçÃÒ­n¦p¦ó¿é¤JªÅ¥Õªü?´N¬O¿ï¿ù¤§«á,¥i¥H¦A¿ï¦¨ªÅ¥Õ
PKKO

TOP

¥»©«³Ì«á¥Ñ HUNGCHILIN ©ó 2015-3-1 23:53 ½s¿è

¦^´_ 5# PKKO

¿ï¿ù¤§«á ­n¦A¥Î¿ïªº ªÅ¥Õ ´N­n¦b²M³æ¤º¦³¤@ªÅ¥Õ¿ï¶µ
´N¬ODATA ­n¦³ªÅ¥Õ®æ
´N¬OÅçÃÒ²M³æ¦³ªÅ¥Õ®æ

±z¤W­±ªºµ{¦¡´Nºñ¦âªº¨º¦æ¦³°ÝÃD.Åܦ¨µù¸Ñ´N¥i¥H°õ¦æ
Public Sub AAAAAA()
With Me
        cc = .Rows(1).Find("©Ò¦³³æ¦ì", Lookat:=xlWhole).Column '¥N¸¹ªºColumn
        Set myRange = .Range("A2:A" & .Range("A65536").End(xlUp).Row) '¸ê®ÆÅçÃÒ¥Î
        For Each e In myRange
            ARR = ARR & "," & e
        Next
        
    MsgBox ARR
        
       ' ARR = Split(Mid(ARR, 2, 10000), ",") '¬Ý¹L°}¦Cªº­È½T©w¨S°ÝÃD
        With .Range("d2:d10").Validation
            .Delete
            .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
            xlBetween, Formula1:=ARR
        End With
End With
End Sub

TOP

¥»©«³Ì«á¥Ñ HUNGCHILIN ©ó 2015-3-2 00:11 ½s¿è

With Me
        CC = .Rows(1).Find("©Ò¦³³æ¦ì", Lookat:=xlWhole).Column '¥N¸¹ªºColumn
        
        
        myRange = .[A1].Resize(.Cells(.Rows.Count, 1).End(xlUp).Row, 1).Value
      
        
        For Each e In myRange
            ARR = ARR & "," & e
        Next

'       ' ARR = Split(Mid(ARR, 2, 10000), ",") '?
        With .Range("d2:d10").Validation
            .Delete
            .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
            xlBetween, Formula1:=ARR
        End With
End With

TOP

¦^´_ 5# PKKO
¸ê®ÆÅçÃÒ­n¦p¦ó¿é¤JªÅ¥Õ,ª½±µ²MªÅ.
  1. '** ¸ê®ÆÅçÃÒ¿ï³æ¨Ó·½
  2.     '1 ¸ê®ÆÅçÃÒ¿ï³æ¨Ó·½¬OÀx¦s®æ¦ì¸m:¦ý»Ý¬O¬O¦P¤@¤u§@ªíªºÀx¦s®æ
  3.     'With Range("d2:d" & allr).Validation
  4.     '   .Delete
  5.     '    .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
  6.     '    xlBetween, Formula1:="=" & Range("A2:A10").Address
  7.     'End With
  8.    
  9.    '2 ¸ê®ÆÅçÃÒ¿ï³æ¨Ó·½¥Î ","³s±µªº¦r¦ê
  10.     With Sheets("Âà´«ªí")
  11.         myRange = .Range("s2:s" & .Range("s65536").End(xlUp).Row) '¸ê®ÆÅçÃÒ¥Î
  12.     End With
  13.     '** °}¦CÂà´«¦¨¦r¦ê
  14.    
  15.     '** Join ¨ç¼Æ ¶Ç¦^¤@­Ó¦r¦ê¡A¸Ó¦r¦ê¬O³z¹L³sµ²¬Y­Ó°}¦C¤¤ªº¦h­Ó¤l¦r¦ê¦Ó«Ø¥ßªº¡C
  16.     '** myRange°Ñ·ÓÀx¦s®æ¬O¬°¤Gºû°}¦C,»ÝÂন¤@ºû°}¦C¤è¥i¬°Join ¨ç¼Æ¨Ï¥Î
  17.     'arr = Join(Application.Transpose(myRange), ",")
  18.    
  19.     '** ¶]°j°é¨ú±o¦r¦ê
  20.     'For Each e In myRange
  21.     '   arr = arr & "," & e
  22.     'Next
  23.      
  24.     '** Split ¨ç¼Æ ¶Ç¦^¤@­Ó³¯¦C¯Á¤Þ±q¹s¶}©lªº¤@ºû°}¦C¡A¥¦¥]§t«ü©w¼Æ¥Øªº¤l¦r¦ê¡C
  25.     '** arr = Split(Mid(arr, 2, 10000), ",") '¿ù»~ªº¥Îªk,¦]¬° arr ¶Ç¦^°}¦C,
  26.     '** arr ¶·¬O ¦r¦ê.
  27.    
  28.     '************************************************
  29.    
  30.     With Range("d2:d" & allr).Validation
  31.         .Delete
  32.         .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
  33.         xlBetween, Formula1:=Join(Application.Transpose(myRange), ",") '<- arr
  34.     End With
½Æ»s¥N½X
·P®¦ªº¤ß......(¦b³Â»¶®a±Ú°Q½×°Ï.¥Î¤ß¾Ç²ß·|¦³¶i¨Bªº)
¦ý¸ê·½µL­­,«á´©¦³­­,  ¤@¤Ñ1¤¸ªºÃÙ§U,¤H¤H¦³¯à¤O.

TOP

¦^´_ 8# GBKEE


    ·PÁÂ,­ì¨Ó¦p¦¹!
PKKO

TOP

¦^´_ 6# HUNGCHILIN

ª©¥D¤j¤j,¥i§_½Ð±Ð±z¨Ï¥Îªº With Me => ³o­ÓMe ¥Nªíªº¬O?
PKKO

TOP

        ÀR«ä¦Û¦b : ºÉ¦h¤Ö¥»¥÷¡A´N±o¦h¤Ö¥»¨Æ¡C
ªð¦^¦Cªí ¤W¤@¥DÃD