PJChen

回復 10# Andy2483
您好,
我把程式放進檔案中了,准大的程式還是無法運算,
請幫我看下該怎麼讓它執行?
~~感謝~~
資料驗算.rar (17.02 KB)

下載次數: 4

2023-4-26 22:23

Andy2483

回復 11# PJChen


    謝謝前輩回復,謝謝論壇,謝謝各位前輩
前輩這帖範例欄位不一樣,剛好可以請前輩試改看看
後學在#4樓 跟 #8樓都有心得註解可以學習,後學說明不清楚或哪裡不懂得,請提出疑問

祝 成功

ML089

C17 陣列公式(要使用三鍵輸入公式)
=SUM(IFERROR(TEXT(RIGHT(TEXT(RIGHT("X"&TRIM(RIGHT(LEFT(SUBSTITUTE(TRIM(MID(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A17,"(",","),")",","),",",REPT(" ",99)),ROW($1:$99)*99-98,99)),"-",REPT(" ",99)),{1,1,1,1,1,2,2,2,2,2}*99),99)),{2,3,4,5,6,2,3,4,5,6}),),{1,2,3,4,5,1,2,3,4,5}),"0;;;")*{-1,-1,-1,-1,-1,1,1,1,1,1}+{0,0,0,0,0,1,1,1,1,1},""))

准提部林

回復 11# PJChen


自訂函數用法//三種
資料驗算-1.rar (14.22 KB)

下載次數: 4

2023-4-27 10:36

PJChen

回復 14# 准提部林

准大好,
請教自定義 Function GetSerial(ST$),程式與要計算的檔案放在一起,就可以計算,
但因為我是把程式與計算檔分開存放,但我做了指定仍是無作用??

Function GetSerial(ST$)
Dim Sh As Worksheet, W As Workbook
Set W = Workbooks("出貨文件_PO.xlsx"): Set Sh = W.Sheets("箱號計算"): Sh.Activate

Dim a, Tr, V1%, V2%, S%
If InStr(ST, "(") = 0 Then ST = "(" & ST
ST = Split(Replace(ST, ")", ""), "(")(1)
For Each a In Split(ST, ",")
    Tr = Split(a & "-" & a, "-")
    V1 = Val(StrReverse(Mid(Val(StrReverse(Tr(0) & 1)), 2)))
    V2 = Val(StrReverse(Mid(Val(StrReverse(Tr(1) & 1)), 2)))
    If V1 + V2 <> 0 Then S = S + Abs(V2 - V1) + 1
Next a
If S > 0 Then GetSerial = S Else GetSerial = ""
End Function