[µo°Ý] ¦p¦ó±N¨C­Ó®É¬q¤ºªº«ü©w¼Æ­È©â¥X

donod

¦p¦ó§Q¥ÎVBA¡A±N¨C1¤ÀÄÁªº"OPEN""HIGH""LOW""CLOSE"¼Æ­È(¦pSheet1¤¤ªº¥ªÃä¨Ò¤l)¡AÂର¨C15¤ÀÄÁ(9:15-9:29, 9:30-9:44, 9:45-9:59, 10:00-10:14,...µ¥µ¥)¡A¨ä¤º(15¤ÀÄÁ)®É¬qªº²Ä1­Ó"OPEN"¼Æ­È¡A³Ì"HIGH"¼Æ­È¡A³Ì"LOW"¼Æ­È¤Î³Ì«áªº1­Ó"CLOSE"¼Æ­È¡A±N³o¨Ç¼Æ­È©â¥X¨Ó¡A­«·s±Æ¦C(¦pSheet1¤¤ªº¥kÃä¨Ò¤l)¡AÁÂÁÂ!
15min.rar (53.96 KB)

¤U¸ü¦¸¼Æ: 8

2013-8-23 17:50

GBKEE

¦^´_ 21# handsometrowa
Dim AR(),  ³o¸Ì«Å§i¤@²Õ°}¦CÅܼƧڤF¸Ñ                -> ÁA¸Ñ¥¦¬O°ÊºA°}¦C,ªÅªº°}¦C??
ReDim AR(0)  ¬°¤°»ò³o¸Ì°¨¤W´N­n­«·s©w¸q°}¦CÅܼƩO?   ->·s¼W¤@¤¸¯À (§@¬°ªíÀY¥Îªº)  
­«·s§â¤¸¯À½á¤©µ¹ AR(0)  ³o¬O¤°»ò·N«ä                  -> ½Ð«öF8 ³v¦æ°õ¦æµ{¦¡,¹L¤F¦æ AR(0) = .[A1:F1] ¨ì[À˵ø]-[°Ï°ìÅܼƵøµ¡] ¬ÝAR(0)ªº¤º®e
¨Ò¦p  .[B5:F9] => ¤Gºû°}¦C( 1 To  9 - 4 , 1 To  5 [BÄæ - FÄ檺 Äæ¼Æ] ) =>¤Gºû°}¦C( ¦C¼Æ , øó¼Æ )

handsometrowa

¦^´_ 7# GBKEE


    G¤j¡A·Q½Ð°Ý±zÃö©ó³oÃä°}¦CÅÞ¿è°ÝÃD

Option Explicit
Sub Ex()
    Dim AR(),  ³o¸Ì«Å§i¤@²Õ°}¦CÅܼƧڤF¸Ñ   Rng As Range, i As Integer, A(1 To 6), T As Integer   
    ReDim AR(0)  ¬°¤°»ò³o¸Ì°¨¤W´N­n­«·s©w¸q°}¦CÅܼƩO?   
                                                        ¦Ó¥B§Ú¦³¤@­ÓºÃ°Ý  AR(0) ¬O³o­Ó°}¦C¤¸¯À¤¤ªº²Ä¤@­ÓÅܼÆ?  ÁÙ¬O ­«·s«Å§i¾ã­Ó°}¦CÅܼÆ
    With Sheets("Sheet1")
        .[B:B].Replace "000", "00", xlPart         '­×§ï¬°®É¶¡®æ¦¡ **********
        AR(0) = .[A1:F1]   

³o¬O§Ú²Ä¤G­ÓºÃ´bªºÂI¢I@"  ­«·s§â¤¸¯À½á¤©µ¹ AR(0)  ³o¬O¤°»ò·N«ä  
·N«ä¬O   
AR(0) = A1  
AR(1)=B1
AR(2)=C1
AR(3)=D1
AR(4)=E1
AR(5)=F1   

OR  

AR(0) ¸Ì­±´N§t¦³  ¤»­Ó¤¸¯À??    §Úı±o©Ç©Çªº ©Ò¥H¯S¦¹½Ð±Ð@@

¥H¤WÁÂÁÂ~^^

donod

¦^´_ 19# GBKEE
©ú¥Õ¤F¡A·PÁÂGBKEEª©¤j±Ð¾É!

GBKEE

¦^´_ 18# donod

1. Option Explicit
   
2. Sub Ex()
   
3.     Dim D As Variant, E As Range, b As Date
   
4.     With Workbooks("A.xls").Sheets("Sheet1")
   
5.         For Each E In .Range("b2", .[b2].End(xlDown))
   
6.             D = Split(E.Offset(, -1), "/")
   
7.             b = E + #12:01:00 AM#
   
8.             E.Value = E + #12:01:00 AM#
   
9.             E.Offset(, 6) = D(0) & D(1) & Application.Text(b, "hhmm")
   
10.         Next
    
11.     End With
    
12. End Sub

½Æ»s¥N½X

donod

³o­Ó¤£¦æ

1. Sub Ex()
   
2. 
   
3. 
   
4. 
   
5.     Dim D As Variant, E As Range, b As Date
   
6.   
   
7.     Windows("A.xls").Activate
   
8.     Sheets("Sheet1").Select
   
9.       
   
10.     With ActiveWorkbook.Sheets(1)
    
11.    
    
12.    
    
13.         For Each E In Range("b1", [b1].End(xlDown))
    
14. 
    
15.           D = Split(E.Offset(, -1), "/")
    
16.         
    
17.           b = E + #12:01:00 AM#
    
18. 
    
19.           E.Value = E + #12:01:00 AM#
    
20. 
    
21.           E.Offset(, 6) = D(0) & D(1) & Application.Text(b, "hhmm")
    
22. 
    
23.         Next
    
24.    
    
25.    
    
26.     End With
    
27. 
    
28. End Sub

½Æ»s¥N½X

B.rar (11.78 KB)

¤U¸ü¦¸¼Æ: 3

2013-9-5 18:12

donod

½Ð°Ý¦pªG­n³B²zªºÀÉ®×A.xls¤w¸g¥´¶}¡A¥H¤WªºVBAÀɮצbB.xlsm¡A¦p¦ó¼g¤~¯à¹ïA.xls§@¦Û°ÊÅÜ°Ê¡AÁÂÁÂ!

donod

¦^´_ 15# GBKEE
¸ò±qGBKEEª©¤jªº«ü¾É¡A¦¨¥\¤F!¦A¤T·PÁÂ!

1. Option Explicit
   
2. 
   
3. Sub Ex()
   
4. 
   
5.     Dim D As Variant, E As Range, b As Date
   
6. 
   
7.     For Each E In Range("b2", [b2].End(xlDown))
   
8. 
   
9.         D = Split(E.Offset(, -1), "/")
   
10.         
    
11.         b = E + #12:01:00 AM#
    
12. 
    
13.         E.Value = E + #12:01:00 AM#
    
14. 
    
15.         'E.Offset(, 6) = D(0) & D(1) & Replace(E.Text, ":", "")
    
16.          E.Offset(, 6) = D(0) & D(1) & Application.Text(b, "hhmm")
    
17. 
    
18.     Next
    
19. 
    
20. End Sub

½Æ»s¥N½X

GBKEE

¦^´_ 14# donod
¸Õ¸Õ¬Ý

1. Option Explicit
   
2. Sub Ex()
   
3.     Dim D As Variant, E As Range
   
4.     For Each E In Range("b2", [b2].End(xlDown))
   
5.         D = Split(E.Offset(, -1), "/")
   
6.         E.Value = E + #12:01:00 AM#
   
7.         E.Offset(, 6) = D(0) & D(1) & Replace(E.Text, ":", "")
   
8.     Next
   
9. End Sub

½Æ»s¥N½X

ÁÙ¬O

1. Sub Ex1()
   
2.     Dim D As Variant, E As Range, b As Date
   
3.     For Each E In Range("b2", [b2].End(xlDown))
   
4.         b = E + #12:01:00 AM#
   
5.         D = Split(E.Offset(, -1), "/")
   
6.         E.Offset(, 6) = D(0) & D(1) & Application.Text(b, "hhmm")
   
7.     Next
   
8. End Sub

½Æ»s¥N½X

donod

¦A½Ð±Ð¦p¦ó±NBÄ檺®É¶¡¦Û°Ê¼W¥[1¤ÀÄÁ(e.g. 9:15Âର9:16)
¤§«á¦bHÄæÅã¥Ü¬ÛÃöDate Code(e.g. 5130916 ¥Nªí5¤ë13¤é9:16)
ÁÂÁÂ!
A1min.rar (140.66 KB)

¤U¸ü¦¸¼Æ: 5

2013-9-5 12:58