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

[µo°Ý] ½Ð°Ý¦p¦ó±N¤º®e¦³¤¤µØ¥Á°ê¤é´Áªº¦r²´§ì¥X¨ÓÂন¦è¤¸¤é´Á?

¦^´_ 20# c_c_lai
  1. Sub Ex_¤é´Á¼Æ­È()
  2.     Dim i  As Long, xl_Year As Variant
  3.     With Range("B1:B" & [A1].End(xlDown).Row)
  4.         .Cells = "=MID(RC[-1],10,10)"
  5.         .Value = .Value
  6.         .Replace "µo", ""
  7.         For i = 1 To .Count
  8.             xl_Year = Split(.Cells(i), "¦~")
  9.             xl_Year(0) = xl_Year(0) + 1911 & "¦~"
  10.             .Cells(i) = Trim(Join(xl_Year, ""))
  11.         Next
  12.         .Cells.Replace "¦~", "/", xlPart
  13.         .Cells.Replace "¤ë", "/"
  14.         .Cells.Replace "¤é", ""
  15.         .Offset(, -1).Resize(, 2).Sort Key1:=.Cells(1), Order1:=xlAscending, Header:=xlNo
  16.         .Offset(, -1).Resize(, 2).Select         'Offset(, -1) ->¥ª²¾1Äæ 'Resize(, 2)  ->ÂX¥R¬°¨âÄæ
  17.         '.Cells.Sort Key1:=.Cells(1), Order1:=xlAscending, Header:=xlNo
  18.     End With
  19. End Sub
½Æ»s¥N½X
·P®¦ªº¤ß......(¦b³Â»¶®a±Ú°Q½×°Ï.¥Î¤ß¾Ç²ß·|¦³¶i¨Bªº)
¦ý¸ê·½µL­­,«á´©¦³­­,  ¤@¤Ñ1¤¸ªºÃÙ§U,¤H¤H¦³¯à¤O.

TOP

¦^´_ 21# GBKEE
·PÁ±zªº«ü¾É¡A§Ú¤S¾Ç¨ì¤F .Offset(, -1).Resize(, 2)
ªºÀ³¥Î¡A¹ê¦b«D±`¹ê¥Î¡C¦A¦¸»¡ÁnÁÂÁ¡I

TOP

¥»©«³Ì«á¥Ñ Hsieh ©ó 2013-6-6 23:47 ½s¿è

¦^´_ 1# freeffly

  1. Function ChDate(DateStr As String)
  2. 'DateStr¥²¶·¬O¥]§t"¤¤µØ¥Á°ê¦~¤ë¤é"ªº¦r¦ê
  3. Dim Mystr As String, s%
  4.    s = InStr(DateStr, "¤¤µØ¥Á°ê") + 4
  5.    Mystr = Mid(DateStr, s, InStr(s, DateStr, "¤é") - s + 1)
  6.    ChDate = CDate(Replace(Mystr, Val(Mystr) & "¦~", Val(Mystr) + 1911 & "¦~"))
  7. End Function
½Æ»s¥N½X
¾Ç®üµL²P_¤£®¢¤U°Ý

TOP

¥»©«³Ì«á¥Ñ c_c_lai ©ó 2013-6-7 07:12 ½s¿è
¦^´_  freeffly
Hsieh µoªí©ó 2013-6-6 23:37

ÆZ¹ê¥Îªº¥\¯à¨ç¼Æ¡A·PÁ±z¡C¥t¥~§Ú¥[¤W¤F Instr() ±ø¥ó¤£¦¨¥ß®É¤§³B¸Ì¡G
  1. Sub Test()
  2.     Dim txt As String
  3.    
  4.     txt = CEDate([A1])
  5.     MsgBox IIf(txt = "", """¤¤µØ¥Á°ê"" ¦r¦ê¨Ã¤£¦s¦b¡I", "Year = " & txt)
  6. End Sub

  7. Function CEDate(DateStr As String)    ' ¡uC.E.¡v¬O¡uCommon Era¡vªºÁY¼g¡A·N¬°¡u¤½¤¸¡v
  8.     '  DateStr¥²¶·¬O¥]§t"¤¤µØ¥Á°ê¦~¤ë¤é"ªº¦r¦ê
  9.     Dim Mystr As String, s%
  10.    
  11.     s = InStr(DateStr, "¤¤µØ¥Á°ê") + 4
  12.     If s = 4 Then CEDate = "": Exit Function
  13.     Mystr = Mid(DateStr, s, InStr(s, DateStr, "¤é") - s + 1)
  14.     CEDate = CDate(Replace(Mystr, Val(Mystr) & "¦~", Val(Mystr) + 1911 & "¦~"))
  15. End Function
½Æ»s¥N½X
§_«h·|²£¥Í¥H¤Uªº¿ù»~°T®§¡G
°õ¦æ¶¥¬q¿ù»~ '13'¡G
«¬ºA¤£²Å¦X

TOP

¦^´_ 23# Hsieh
¦^´_ 21# GBKEE
§Ú±N GBKEE ª©¤jªº¼Ò²Õ¡B¥[¤W Hsieh ª©¤j´£¨Ñªº Function¡A
¨âªÌ²Õ¦X«áÁÙÆZ¹ê¥Îªº¡G
  1. Sub Ex_¤é´Á¼Æ­È3()
  2.     Dim i  As Long, xl_Year As Variant
  3.     With Range("B1:B" & [A1].End(xlDown).Row)
  4.         For i = 1 To .Count
  5.             .Cells(i) = ChDate(.Cells(i).Offset(, -1))
  6.             If .Cells(i) <> "" Then .Cells(i).Offset(, 1) = CEDate(.Cells(i))
  7.         Next
  8.         '  .Offset(,-1)¡G ¥ª²¾ 1 Äæ (B -> AÄæ)¡F Resize(,3)¡G ±qAÄæ°_ÂX¥R¬°¤TÄæ (A¡BB¡BC ¤TÄæ)
  9.         '  .Cells(1) = "2012¦~10¤ë12¤é"  (B Äæ)¡A .Cells(1).Offset(, 1) = "2012/10/12"  (C Äæ)
  10.         '  .Offset(, -1).Resize(, 3).Sort Key1:=.Cells(1), Order1:=xlAscending, Header:=xlNo
  11.         .Offset(, -1).Resize(, 3).Sort Key1:=.Cells(1).Offset(, 1), Order1:=xlAscending, Header:=xlNo
  12.     End With
  13. End Sub
½Æ»s¥N½X
  1. Function ChDate(DateStr As String)
  2.     '  DateStr¥²¶·¬O¥]§t"¤¤µØ¥Á°ê¦~¤ë¤é"ªº¦r¦ê
  3.     Dim Mystr As String, s%
  4.    
  5.     s = InStr(DateStr, "¤¤µØ¥Á°ê") + 4
  6.     If s = 4 Then ChDate = "": Exit Function
  7.     Mystr = Mid(DateStr, s, InStr(s, DateStr, "¤é") - s + 1)
  8.     ChDate = Replace(Mystr, Val(Mystr) & "¦~", Val(Mystr) + 1911 & "¦~")
  9. End Function

  10. Function CEDate(DateStr As String)    '  ¡uC.E.¡v¬O¡uCommon Era¡vªºÁY¼g¡A·N¬°¡u¤½¤¸¡v
  11.     CEDate = CDate(DateStr)
  12. End Function
½Æ»s¥N½X
ÁÂÁ¨â¦ìª©¤jªº¹©¤OÀ°¦£¡C

TOP

¦^´_ 23# Hsieh


    ÁÂÁ¶Wª©¦^ÂÐ
   ¥­±`´X¥G¤£·|¥h¥Î¦Û­q¨ç¼Æ¤è¦¡
   ¤S¦h¤@­Ó¥i¥H¬ã¨sªºªF¦è¤F
¦r¨å¨â¦U¦r ÁÙ¯uÃø²z¸Ñ

TOP

        ÀR«ä¦Û¦b : ¤£­n¤p¬Ý¦Û¤v¡A¦]¬°¤H¦³µL­­ªº¥i¯à¡C
ªð¦^¦Cªí ¤W¤@¥DÃD