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

[µo°Ý] ¦p¦ó±N¼Ò²Õ¤ºªºÅܼÆSet¬°ÅܼÆ? (¨Ò¦pcontrolsªº¥\¯à)

[µo°Ý] ¦p¦ó±N¼Ò²Õ¤ºªºÅܼÆSet¬°ÅܼÆ? (¨Ò¦pcontrolsªº¥\¯à)

°²³]§Ú¦³s1~s100ªºÅܼÆ
¦buserform¤W­±¥i¥H¨Ï¥Î
For j = 1 To 100
    Set ctrl1 = ("s" & j)
    If ctrl1 = " " Then ctrl1 = ""
Next

¦ý¬O¦b¼Ò²Õ¤º(«Duserform)«oµLªk³o¼Ë¨Ï¥Î
¸Ó¨Ï¥Î¦óºØ¬ÛÃö¥\¯àªºÃöÁä¦r©O~?
PKKO

¦^´_ 1# PKKO
±NUserFormªºÅܼƦWºÙ,¦b¤@¯ë¼Ò²Õ¤º(«Dª«¥ó¼Ò²Õ(UserForm,ThisWorkbook,Sheet1... )
«Å§i¬°³o±M®×ªº Public ¤½¥ÎÅܼÆ,¦p¦¹UserFormªºÅܼÆ,¥i¦b¤@¯ë¼Ò²Õ¤º©I¥s¨ì
·P®¦ªº¤ß......(¦b³Â»¶®a±Ú°Q½×°Ï.¥Î¤ß¾Ç²ß·|¦³¶i¨Bªº)
¦ý¸ê·½µL­­,«á´©¦³­­,  ¤@¤Ñ1¤¸ªºÃÙ§U,¤H¤H¦³¯à¤O.

TOP

¦^´_ 2# GBKEE


    ¶Wª©¤j¤j±z¦n,©êºp,§Ú»¡©úªº¤£°÷²M·¡

§Ú·Q°Ýªº¬O:

¦b¼Ò²Õ¤ºªºÅܼư²³]¦³100­Ó
¤À§O©R¦W¬°S1~S100
§Ú§Æ±æ³z¹L§PÂ_¬OIF S1 ="HH" THEN S1 = "YY"
¦ý³o²³æªºµ{¦¡½X­n¼g¤@¦Ê¦æ(S1~S100)¤]®¼¨¯­Wªº
¬O§_¦³¿ìªk¥i¥H

FOR I = 1 TO 100
  SET Sxx="S" & I
  IF Sxx ="HH" THEN Sxx = "YY"
NEXT
PKKO

TOP

¦^´_ 2# GBKEE

¶Wª©¤j¤j±z¦n,¥D­n¬OÀɮ׸̭±ªº³o¬qµ{¦¡½X
  1. With Sheets("¸ê®Æ®w")
  2.     If Not .Cells.Find(sheetname, Lookat:=1) Is Nothing Then
  3.         c = .Cells.Find(sheetname, Lookat:=1).Column
  4.         
  5.         s1 = .Cells(2, c): s2 = .Cells(3, c): s3 = .Cells(4, c): s4 = .Cells(5, c)
  6.         s5 = .Cells(6, c): s6 = .Cells(7, c): s7 = .Cells(8, c): s8 = .Cells(9, c)
  7.         s9 = .Cells(10, c): s10 = .Cells(11, c): s11 = .Cells(12, c)
  8.         s12 = .Cells(13, c): s13 = .Cells(14, c): s14 = .Cells(15, c)
  9.         s15 = .Cells(16, c): s16 = .Cells(17, c)
  10.         
  11.         Sheets("DIR").Activate
  12.     Else
  13.         MsgBox "½Ð­«·s½T»{!"
  14.     End If

  15. End With
½Æ»s¥N½X
¦ý§Ú·Q§âS1~S16°µÂI§ïÅÜ
©Ò¥H·Q¦bµ{¦¡½X³Ì«á­±¥[¤W¤@¬q
  1. For I = 1 To 16
  2.     Set ctrl = ("s" & I)
  3.     If ctrl = " " Then ctrl = ""
  4. Next
½Æ»s¥N½X
¦ý¬O·|¥X²{¿ù»~°T®§" «¬ºA¤£²Å¦X"
PKKO

TOP

¦^´_ 4# PKKO
¸Õ¸Õ¬Ý

¼Ò²Õ³»ºÝ«Å§i¨p¥ÎÅܼÆ
Dim S As Range
  1.   With Sheets("¸ê®Æ®w")
  2.     If Not .Cells.Find(sheetname, Lookat:=1) Is Nothing Then
  3.         C = .Cells.Find(sheetname, Lookat:=1).Column
  4.         Set S = .Cells(2, C).Resize(16)
  5.         Sheets("DIR").Activate
  6.     Else
  7.         MsgBox "½Ð­«·s½T»{!"
  8.     End If

  9. End With
½Æ»s¥N½X
  1. For Each E In S
  2.     Set ctrl = S
  3.     If ctrl = " " Then ctrl = ""
  4. Next
½Æ»s¥N½X
·P®¦ªº¤ß......(¦b³Â»¶®a±Ú°Q½×°Ï.¥Î¤ß¾Ç²ß·|¦³¶i¨Bªº)
¦ý¸ê·½µL­­,«á´©¦³­­,  ¤@¤Ñ1¤¸ªºÃÙ§U,¤H¤H¦³¯à¤O.

TOP

¥»©«³Ì«á¥Ñ PKKO ©ó 2014-12-15 10:40 ½s¿è

¦^´_ 5# GBKEE

·PÁ¶Wª©¤j¤j,¤p§Ì¾Ç²ß¤F
¥H¤U¬O¤p§Ì¹ê»Úªºµ{¦¡½X(¾Ç²ß«á§ïª©)
§ïª©­ì¦]¬O
­ì¥»S1~S16³£¬O»Ý­nÀ³¥Îªº,²{¦b¤Ï¦ÓS1~S16¨S¦³­È¤F
¦]¦¹±N¤j¤j±zªºS§ï¦¨Q(¦]¬°­ì¥»S¤w¸g¦³¨Ï¥Î),¥ÎQªº­È©î¶}¦¨¬°°}¦C,´N¥i¥H¥Î°}¦C¥hŪ­È
¥Ø«e¹êÅ秹¦¨,¤w¥i§¹¬ü¨Ï¥Î,¦A¦¸·PÁ¶Wª©¤j¤j
  1. Public q As Range, myArray()'¼Ò²Õ³»ºÝ,¦]¬°§Oªº¼Ò²Õ¦³¥Î¨ì¦]¦¹«Å§ipublic
  2. With Sheets("¸ê®Æ®w")
  3.     If Not .Cells.Find(sheetname, Lookat:=1) Is Nothing Then
  4.         c = .Cells.Find(sheetname, Lookat:=1).Column
  5.         Set q = .Cells(2, c).Resize(16)
  6.         Sheets("DIR").Activate
  7.     Else
  8.         MsgBox "½Ð­«·s½T»{!"
  9.     End If

  10. End With
  11.     c = 0
  12.     ReDim myArray(0 To 15)
  13.     For Each E In q
  14.         Set ctrl = E'¨ä¹ê³oÃä¥i¥H¤£»Ý­n¤F
  15.         If ctrl = " " Then myArray(c) = "" Else myArray(c) = ctrl'§âctrl´«¦¨E´N¥i¥H¤F
  16.         c = c + 1
  17.     Next
½Æ»s¥N½X
PKKO

TOP

¦^´_ 6# PKKO
  1. Dim i As Integer
  2.     With Sheets(1)
  3.         If Not .Cells.Find(sheetname, Lookat:=1) Is Nothing Then
  4.             c = .Cells.Find(sheetname, Lookat:=1).Column
  5.             Set q = .Cells(2, c).Resize(16)
  6.             Sheets("DIR").Activate
  7.         Else
  8.             MsgBox "½Ð­«·s½T»{!"
  9.         End If
  10.     End With
  11.     myArray = Application.WorksheetFunction.Transpose(q)
  12.     For i = 1 To q.Count
  13.         If ctrl = " " Then myArray(i) = "" Else myArray(i) = ctrl
  14.    
  15.     Next
  16. '***************************
  17.     'For i = 1 To UBound(myArray)
  18.     '   If ctrl = " " Then myArray(i) = "" Else myArray(i) = ctrl
  19.     'Next
½Æ»s¥N½X
'***************************
·P®¦ªº¤ß......(¦b³Â»¶®a±Ú°Q½×°Ï.¥Î¤ß¾Ç²ß·|¦³¶i¨Bªº)
¦ý¸ê·½µL­­,«á´©¦³­­,  ¤@¤Ñ1¤¸ªºÃÙ§U,¤H¤H¦³¯à¤O.

TOP

¥»©«³Ì«á¥Ñ PKKO ©ó 2014-12-15 23:43 ½s¿è

¦^´_ 7# GBKEE


    ·PÁ¶Wª©¤j¤j,¤S¦A¦¸¾Ç²ß¤F
¦ý±zªº·N«ä¦]¸Ó¬O§ï¬°³o¼Ë,¦]¬°ctrl¨Ã¨S¦³¨Ï¥Îªº¥²­n¤F
  1. myArray = Application.WorksheetFunction.Transpose(q)
  2.     For YY = 1 To q.Count
  3.         If myArray(YY) = " " Then myArray(YY) = ""
  4.     Next
½Æ»s¥N½X
¹êÅç¹L¤F,¦¹¤èªk¤]§¹¥þ¥i¦æ,¥B¼¶¼g³t«×§ó¥[§Ö³t
PKKO

TOP

        ÀR«ä¦Û¦b : ¯à·F¤£·F¡A¤£¦p­W·F¹ê·F¡C
ªð¦^¦Cªí ¤W¤@¥DÃD