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

[µo°Ý] ¿z¿ï?ÃöÁä¦r?¬d¸ß?

¦^´_ 60# GBKEE


     ÁÂÁ GBKEE ª©¤j¡A±zªº¤è¦¡¤£§¹¥þ¬O§Ú·Q­nªºµ²ªG¡A§Ú¬O¥Î¤F³Ì²Âªº¤è¦¡Â¶¤F¤@¤j°é°µ¥X§Ú­nªºµ²ªG
°_¥ý§Ú¬O¥Î¡u=SUBSTITUTE(A4,"0¬B","")¡v³oºØ¤è¦¡¡A¦ýrunªº®É­Ô¤@ª½¥X²{sub¬ÛÃö¦rªº¿ù»~¡A©Ò¥H¤~§ï¬°Replace¡A
¦ý¤£¾å±o GBKEE ª©¤j¹ï§Ú³o¼Ëªº°µªk¦³µL§ó¦nªº«Øij¡AÁÙ¦³§Ú¬°¤F¤ñ·ÓÅã¥Üªºµ²ªG¡A¥»¨Ó¥Î
Sheets("¤u§@ªí1").[B1] = t
Sheets("¤u§@ªí1").[B2] = t1
¡ô³o¼Ë¨Ó¹ï·Ó¤£¦PªºÅã¥Üµ²ªG¡A¦ý¤£¾å±o¬°¤°»ò³o¼Ë°õ¦æ·|¦³·í±¼ªº±¡§Î
  1. Option Explicit
  2. Private Sub Worksheet_Change(ByVal Target As Range)
  3.     '¢°¢±,¢²¢³¢´¸U¢µ¤d¢¶¦Ê¢·¤Q
  4.     Dim Dot As Long, m As String, t As String, t1 As String
  5.     m = "0¸U0¥a0¨Õ0¬B0"
  6.     Dot = Sheets("¤u§@ªí1").[A1]
  7.     If Len(CStr(Dot)) = 1 Then m = "0"
  8.     If Len(CStr(Dot)) = 2 Then m = "0¬B0"
  9.     If Len(CStr(Dot)) = 3 Then m = "0¨Õ0¬B0"
  10.     If Len(CStr(Dot)) = 4 Then m = "0¥a0¨Õ0¬B0"
  11.     t = Format(Dot, m)
  12.    'Sheets("¤u§@ªí1").[B1] = Format(Dot, m)
  13.     With Sheets("¤u§@ªí1")
  14.     t1 = Replace(t, "¬B0", "¬B")
  15.     t1 = Replace(t1, "0¬B0", "")
  16.     t1 = Replace(t1, "0¬B", "")
  17.     t1 = Replace(t1, "0¨Õ", "")
  18.     Sheets("¤u§@ªí1").[A4] = t1
  19.     End With
  20. End Sub
½Æ»s¥N½X
test2.rar (16.1 KB)

TOP

¦^´_ 61# emma
¥¼©R¦W.png
2013-5-2 00:14
  1. Function Read_Number(MyNum)
  2. n = Right(MyNum, 4)
  3. a = Array("¬B", "¦Ê", "¤d")
  4. k = Len(n)
  5. i = k
  6. Do Until i = 0
  7.   If Val(Mid(n, i, 1)) > 0 And i < k Then
  8.      m = Mid(n, i, 1) & a(j): j = j + 1
  9.      ElseIf i = k Then
  10.      m = IIf(Val(Mid(n, i, 1)) = 0, "", Mid(n, i, 1))
  11.      Else
  12.      m = IIf(Val(Mid(n, i, 1)) = 0, "¹s", Mid(n, i, 1) & a(j))
  13.      j = j + 1
  14.   End If
  15.   t = m & t
  16.   i = i - 1
  17. Loop
  18. For i = 3 To 2 Step -1
  19.   t = Replace(t, String(i, "¹s"), "¹s")
  20. Next
  21. s = Val(Left(MyNum, Len(MyNum) - k))
  22. Read_Number = IIf(s = 0, "", Format(Val(Left(MyNum, Len(MyNum) - k)), "#,##0¸U")) & t
  23. End Function
½Æ»s¥N½X
¾Ç®üµL²P_¤£®¢¤U°Ý

TOP

¦^´_ 61# emma
  1. Option Explicit
  2. Private Sub Ex()
  3.     '¢°¢±,¢²¢³¢´¸U¢µ¤d¢¶¦Ê¢·¤Q
  4.     Dim Dot As Long, m As String, t As String, i  As Integer
  5.     m = "0¸U0¥a0¨Õ0¬B0 "         '¥[¤W¤@ªÅ®æ:³B²z­Ó¦ì¼Æ¬°0
  6.     Dot = Sheets("¤u§@ªí1").[A1]
  7.     t = Format(Dot, m)
  8.     For i = IIf(Len(t) > 10, 3, 1) To Len(m) Step 2
  9.         'IIf(Len(t) > 10, 3, 1) ³B²z¸U¦ì¼Æ¬°0
  10.         t = Replace(t, Mid(m, i, 2), "")
  11.     Next
  12.     Sheets("¤u§@ªí1").[A4] = t
  13.     'Sheets("¤u§@ªí1").[A4] = RTrim(t)
  14.     'LTrim¡BRTrim »P Trim ¨ç¼Æ
  15.     '¶Ç¦^¤@­Ó¨S¦³«eÀYªÅ¥Õ (LTrim)¡B«á­±ªÅ¥Õ (RTrim) ©Î«e«á§¡µLªÅ¥ÕªºVariant (String)¡A
  16. End Sub
½Æ»s¥N½X
·P®¦ªº¤ß......(¦b³Â»¶®a±Ú°Q½×°Ï.¥Î¤ß¾Ç²ß·|¦³¶i¨Bªº)
¦ý¸ê·½µL­­,«á´©¦³­­,  ¤@¤Ñ1¤¸ªºÃÙ§U,¤H¤H¦³¯à¤O.

TOP

¦U¦ì«e½ú­Ì¦n¡A¦]¬°·íªì³o­ÓÀɮ׳]­pªº®É¶¡¤w¤[¡A¥[¤W«Ü¦hµ{¦¡¬O¨Ì¿à¦U¦ì¤j¤j­Ì¨ó§U§¹¦¨ªº¡A©Ò¥H¦³¥\¯à·Q­n°µ·sªº²§°Ê¡A¦ý·Q«Ü¤[³£¸Õ¤£¥X¨Ó¡A¥u¦n¦A«pµÛÁy¥Ö¤W¨Ó¨D§U

ªþ¥ó¤¤ªº¡y¥¼¥X³f²M³æ¡z¤u§@ªí¬O±q¡y¬d¸ß¡z¤u§@ªí¤¤ªº¸ê®Æ·J¾ã¹L¥hªº¡A¥H«e¬O§â¦P¡u¬¡°Êª¬ºA¡v¤Î¡u«~¦W¡vªºµ§¼Æ¾ã¦X¦b¦P¤@¦C¤W¡A
²{¦b·Q­n¨C¤@µ§³£¦C¥X¨Ó¡A¬Û¦Pªº¸ê®Æ¦pªG¤j©ó¤Gµ§¡A´N¦h¤@¦C¤p­pÀ°¦£­pºâµ§¼Æ¡A³Ì«áÁ`µ§¼Æ¬O¥þ³¡ªº¥[Á`¡C
¤£¾å±o¬O§_¯à³o»ò³]©w¡A³Â·Ð¤j¤j­Ì¤F¡AÁÂÁÂ^^


VBAtest13.rar (29.8 KB)

TOP

        ÀR«ä¦Û¦b : ¦h°µ¦h±o¡C¤Ö°µ¦h¥¢¡C
ªð¦^¦Cªí ¤W¤@¥DÃD