¾ã²z¸ê®Æ

Qin

E & F Äæ¬O¨t²Î¾É¥Xªº¸ê®Æ, §Ú·Q§â¥¦¾ã²z¦¨¦p: A - D Äæ³o¯ë.                       
½Ð°Ý¸Ó¦p¦ó¤U¤½¦¡?                       
TestBook1.rar (127.26 KB)

¤U¸ü¦¸¼Æ: 19

2018-9-14 14:39

Andy2483

ÁÂÁ½׾Â,ÁÂÁ¦U¦ì«e½ú
«á¾ÇÂǦ¹©«½m²ßVBA°}¦C,¾Ç²ß¤è®×¦p¤U,½Ð¦U¦ì«e½ú«ü±Ð

°õ¦æ«e:

¤U¸ü (176.69 KB)

2023-6-7 15:13

°õ¦æµ²ªG:

¤U¸ü (251.43 KB)

2023-6-7 15:14

Option Explicit
Sub TEST()
Dim Brr, Crr, V, Z$, Q, i&, T$
'¡ô«Å§iÅܼÆ
Brr = Range([E6], [E65536].End(3))
'¡ô¥OBrrÅܼƬO ¤Gºû°}¦C,¥HEÄæÀx¦s®æ­È±a¤J°}¦C¤¤
Z = "Invoice [#] ######  #*/#*/####"
'¡ô¥OZÅܼƬO¦r¦ê
ReDim Crr(1 To UBound(Brr) - 1, 1 To 4)
'¡ô¥OCrrÅܼƬO ¤GºûªÅ°}¦C,Áa¦V½d³ò1~Brr°}¦CÁa¦V³Ì¤j¯Á¤Þ¦C¸¹-1,¾î¦V½d³ò1~4
For i = 1 To UBound(Brr)
'¡ô³]¶¶°j°é!
   T = Brr(i, 1)
   '¡ô¥OTÅܼƬO °j°éBrr°}¦C­È(¦r¦ê)
   If T Like Z Then V = Split(T, " "): GoTo i01
   '¡ô¦pªG¥H Like ¹Bºâ¤l §P§O(TÅܼƻPZÅܼÆ)¦^¶Ç True?
   '¬O´N¥OV¬O(¥HªÅ¥Õ¦r¤¸¤À³ÎTÅܼƪº¤@ºû°}¦C),¥Oµ{§Ç¸õ¨ìi01¦ì¸mÄ~Äò°õ¦æ
   If Val(T) = 0 Then GoTo i01 Else T = T & " "
   '¡ô¦pªGTÅܼÆÂà¼Æ­È«á¬O 0?¬O´N¥Oµ{§Ç¸õ¨ìi01¦ì¸mÄ~Äò°õ¦æ,
   '§_«h´N¥OTÅܼƳs±µ" "²Õ¦¨¬°·s¦r¦ê
   Crr(i - 1, 1) = V(2): Crr(i - 1, 2) = V(4)
   '¡ô¥OCrr°}¦C²Ä1Äæ¼g¤J(2¯Á¤Þ¸¹V°}¦C­È),
   '¥OCrr°}¦C²Ä2Äæ¼g¤J(4¯Á¤Þ¸¹V°}¦C­È)
   Q = InStr(T, " ")
   '¡ô¥OQÅܼƬO§P©wTÅܼƲÄ1­ÓªÅ¥Õ¦r¤¸ªº¦ì¸m
   Crr(i - 1, 3) = Left(T, Q - 1)
   '¡ô¥OCrr°}¦C²Ä3Äæ¼g¤JTÅܼƪº¥ª°¼(Q - 1)­Ó¦r¤¸
   Crr(i - 1, 4) = Trim(Mid(T, Q + 1))
   '¡ô¥OCrr°}¦C²Ä4Äæ¼g¤JTÅܼƱq(Q + 1)­Ó¦r¥H«áªº¦r¦ê
i01: Next
[A7].Resize(UBound(Crr), 4) = Crr
'¡ô¥O±q[A7]¶}©lÂX®iªº½d³òÀx¦s®æ­È¬OCrr°}¦C­È
Erase Brr, Crr, V
'¡ô¥OÄÀ©ñÅܼÆ
End Sub

­ã´£³¡ªL

¦^´_ 4# Qin


1)
=IF(LEFT(E6,7)="Invoice",--MID(E6,11,6),IF(COUNT(-MID(E7,7,2)),LOOKUP(9^9,A$6:A6),""))

2)
=IF(LEFT(E6,7)="Invoice",--TEXT(SUM(MID(SUBSTITUTE(MID(E6,19,10),"/",REPT(" ",9)),{1,2,3}*9-8,9)*10^{0,2,4}),"0-00-00"),IF(A7="","",LOOKUP(9^9,B$6:B6)))

3)
=IF(A7="","",LEFT(E7,FIND(" ",E7&" ")-1))

4)
=IF(A7="","",MID(E7,FIND(" ",E7&" ")+1,99))

Qin

¦^´_ 2# ­ã´£³¡ªL

­ã¤j
¦³¨Ç¸ê®Æ§ì¤£¨ì, ³Â·Ð§A¦AÀ°§Ú¬Ý¬Ý...
ÁÂÁÂ!!

Book1.rar (82.9 KB)

¤U¸ü¦¸¼Æ: 7

2018-10-31 23:12

Qin

¦^´_ 2# ­ã´£³¡ªL


  ÁÂÁÂ!

­ã´£³¡ªL

¢Ï¢¶¡G
=IF(LEFT(E6,7)="Invoice",--MID(E6,11,6),IF(COUNT(-LEFT(E7)),LOOKUP(9^9,A$6:A6),""))
¡@
¢Ð¢¶¡G¤é´ÁÂà´«¸û·Ð³Â
=IF(LEFT(E6,7)="Invoice",--TEXT(SUM(MID(SUBSTITUTE(MID(E6,19,10),"/",REPT(" ",9)),{1,2,3}*9-8,9)*10^{0,2,4}),"0-00-00"),IF(A7="","",LOOKUP(9^9,B$6:B6)))
¡@
¢Ñ¢¶¡G
=IF(A7="","",LEFT(E7,FIND(" ",E7)-1))
¡@
¢Ò¢¶¡G
=IF(A7="","",MID(E7,FIND(" ",E7)+1,99))