PJChen

　暱稱: PJ

帖子: 913
主題: 150
精華: 0
積分: 1089
點名: 0
作業系統: win10
軟體版本: office 2019
閱讀權限: 50
性別: 女
註冊時間: 2011-8-28
最後登錄: 2023-7-19

論壇榮譽勳章論壇贊助勳章

1^# 跳轉到 » 倒序看帖

打印

字體大小: tT

發表於 2020-3-1 17:59 | 顯示全部帖子

[發問] VBA中的函數式取代

本帖最後由 PJChen 於 2020-3-1 18:00 編輯

Dear,
在工作表中可以自定義函數使公式變得簡潔,但因為用得太多了,當需要修改時,也是很令人混淆...
想改在寫程式時,把公式用一個代碼xc表示,最後變成值,這樣就不用一直把冗長的函數式寫出來,但是"行不通",
請問是否有其他的寫法or需要什麼物件,試了又試總是不行..
            i = "C1:J1"
                  xcol = Range(i).Columns.Count
                  Set xc = "=" & "SUMPRODUCT((飛比!$F$4:$F$70=查帳!$B$3)*(飛比!$AP$3:$BH$3=查帳!C4)*(比菲多!$AP$4:$BH$70))" '查帳_訂購數
                     .Range("C5").Resize(1, xcol).Value = "=" & xc
                     .Range("C5").Resize(1, xcol).Value = .Range("C5").Resize(1, xcol).Value

樓主熱帖

PJChen

　暱稱: PJ

大學生 Rank: 4

帖子: 913
主題: 150
精華: 0
積分: 1089
點名: 0
作業系統: win10
軟體版本: office 2019
閱讀權限: 50
性別: 女
註冊時間: 2011-8-28
最後登錄: 2023-7-19

論壇榮譽勳章論壇贊助勳章

2^#

發表於 2020-3-2 16:16 | 顯示全部帖子

回復 2# jcchiang

改這樣在C5儲存格資料正確,但其他儲存格得出的值會錯誤,原因為在於紅字部份,C4不是$C$4 應能隨著儲存格不同而位移,可是將公式用xc取代時,卻變得不會移動,只是一個死的東西,請問能有其他寫法,可以讓公式活用嗎?
SUMPRODUCT((飛比!$F$4:$F$70=查帳!$B$3)*(飛比!$AP$3:$BH$3=查帳!C4)*(比菲多!$AP$4:$BH$70))

TOP

PJChen

　暱稱: PJ

大學生 Rank: 4

帖子: 913
主題: 150
精華: 0
積分: 1089
點名: 0
作業系統: win10
軟體版本: office 2019
閱讀權限: 50
性別: 女
註冊時間: 2011-8-28
最後登錄: 2023-7-19

論壇榮譽勳章論壇贊助勳章

3^#

發表於 2020-3-2 17:36 | 顯示全部帖子

本帖最後由 PJChen 於 2020-3-2 17:41 編輯

回復 4# jcchiang
我二種都要用,以目前訂購數公式來說,從C5:J5 / C14:J4
VBA中的函數式取代.rar (103.59 KB) 是錯的,它不讓我刪除,請勿下載

最新庫存-查帳.rar (83.12 KB)

VBA中的函數式取代.rar (103.59 KB)

TOP

PJChen

　暱稱: PJ

大學生 Rank: 4

帖子: 913
主題: 150
精華: 0
積分: 1089
點名: 0
作業系統: win10
軟體版本: office 2019
閱讀權限: 50
性別: 女
註冊時間: 2011-8-28
最後登錄: 2023-7-19

論壇榮譽勳章論壇贊助勳章

4^#

發表於 2020-3-2 17:50 | 顯示全部帖子

回復 4# jcchiang
我知道了,要這樣改...感謝您
.Range("C5").Resize(xcol, xcol).Value = 查帳_訂購數
.Range("C5").Resize(xcol, xcol).Value = .Range("C5").Resize(xcol, xcol).Value

TOP

PJChen

　暱稱: PJ

大學生 Rank: 4

帖子: 913
主題: 150
精華: 0
積分: 1089
點名: 0
作業系統: win10
軟體版本: office 2019
閱讀權限: 50
性別: 女
註冊時間: 2011-8-28
最後登錄: 2023-7-19

論壇榮譽勳章論壇贊助勳章

5^#

發表於 2020-3-2 18:33 | 顯示全部帖子

本帖最後由 PJChen 於 2020-3-2 19:00 編輯

回復 6# PJChen
這樣做,資料還是錯吔...除了C5一整列的資料正確,其餘連執行都有問題,卡住了...

查帳_訂購數 = "=" & "SUMPRODUCT((飛比!$F$4:$F$70=$B$3)*(飛比!$AP$3:$BH$3=C4)*(飛比!$AP$4:$BH$70))"
查帳_訂購箱瓶 = "=" & "IF(C5=0,"",INT(C5/$C$3)&IF(MOD(C5,$C$3)=0,""箱"",""箱+""&MOD(C5,$C$3)))"
查帳_廠缺 = "=" & "SUMPRODUCT((飛比!$F$4:$F$70=$B$3)*(飛比!$BJ$3:$CB$3=C4)*(飛比!$BJ$4:$CB$70))"
查帳_廠缺箱瓶 = "=" & "IF(C7=0,"",INT(C7/$C$3)&""箱"")&IF(MOD(C7,$C$3)=0,"",""+""&MOD(C7,$C$3))"
查帳_劃單 = "=" & "SUMPRODUCT((飛比!$F$4:$F$70=$B$3)*(飛比!$CD$3:$CV$3=C4)*(飛比!$CD$4:$CV$70))"
查帳_實出數 = "=" & "SUMPRODUCT((飛比!$F$4:$F$70=$B$3)*(飛比!$CX$3:$DP$3=C4)*(飛比!$CX$4:$DP$70))"
查帳_實出箱瓶 = "=" & "IF(C10=0,"",INT(C10/$C$3)&""箱"")&IF(MOD(C10,$C$3)=0,"",""+""&MOD(C10,$C$3))"
i = "C1:J1"
xcol = Range(i).Columns.Count '看幾筆資料
.Range("C5").Resize(1, xcol).Value = 查帳_訂購數
.Range("C5").Resize(1, xcol).Value = .Range("C5").Resize(1, xcol).Value
.Range("C14").Resize(xcol, xcol).Value = 查帳_訂購數
.Range("C14").Resize(xcol, xcol).Value = .Range("C14").Resize(xcol, xcol).Value
.Range("C6").Resize(1, xcol).Value = 查帳_訂購箱瓶
.Range("C6").Resize(1, xcol).Value = .Range("C6").Resize(1, xcol).Value
.Range("C15").Resize(xcol, xcol).Value = 查帳_訂購箱瓶
.Range("C15").Resize(xcol, xcol).Value = .Range("C15").Resize(xcol, xcol).Value
.Range("C7").Resize(1, xcol).Value = 查帳_廠缺
.Range("C7").Resize(1, xcol).Value = .Range("C7").Resize(1, xcol).Value
.Range("C16").Resize(xcol, xcol).Value = 查帳_廠缺
.Range("C16").Resize(xcol, xcol).Value = .Range("C16").Resize(xcol, xcol).Value
.Range("C8").Resize(1, xcol).Value = 查帳_廠缺箱瓶
.Range("C8").Resize(1, xcol).Value = .Range("C8").Resize(1, xcol).Value
.Range("C17").Resize(xcol, xcol).Value = 查帳_廠缺箱瓶
.Range("C17").Resize(xcol, xcol).Value = .Range("C17").Resize(xcol, xcol).Value
.Range("C9").Resize(xcol, xcol).Value = 查帳_劃單
.Range("C9").Resize(xcol, xcol).Value = .Range("C9").Resize(xcol, xcol).Value
.Range("C10").Resize(xcol, xcol).Value = 查帳_實出數
.Range("C10").Resize(xcol, xcol).Value = .Range("C10").Resize(xcol, xcol).Value
.Range("C19").Resize(xcol, xcol).Value = 查帳_實出數
.Range("C19").Resize(xcol, xcol).Value = .Range("C19").Resize(xcol, xcol).Value
.Range("C11").Resize(xcol, xcol).Value = 查帳_實出箱瓶
.Range("C11").Resize(xcol, xcol).Value = .Range("C11").Resize(xcol, xcol).Value
.Range("C20").Resize(xcol, xcol).Value = 查帳_實出箱瓶
.Range("C20").Resize(xcol, xcol).Value = .Range("C20").Resize(xcol, xcol).Value

複製代碼

TOP

PJChen

　暱稱: PJ

大學生 Rank: 4

帖子: 913
主題: 150
精華: 0
積分: 1089
點名: 0
作業系統: win10
軟體版本: office 2019
閱讀權限: 50
性別: 女
註冊時間: 2011-8-28
最後登錄: 2023-7-19

論壇榮譽勳章論壇贊助勳章

6^#

發表於 2020-3-7 23:31 | 顯示全部帖子

回復 9# jcchiang

本想改善表格,讓它簡潔又RUN快些,好像腦袋變得更想不清楚了...
先請教另個公式問題,快把我搞瘋了....
以下3個公式,直接key在儲存格時都沒問題,但為什麼在VBA中,完全無法執行?
1) 無法執行
xRow = Cells(Cells.Rows.Count, "A").End(xlUp).Row
Range("E3:E" & xRow).Formula = "=IF(E3="",""無交貨"",T3&S3&R3)"
2)無法執行
Range("Z3:Z" & xRow).Formula = "=IF(AB3="","",AB3-Y3)"

3) 無法執行
Range("F2:F" & xRow).Formula = "=IF(MONTH(A3)<>MONTH(A4),SUM(INDIRECT(ADDRESS(MATCH(DATE(YEAR(A3),MONTH(A3),1),A:A,0),COLUMN(D3),3)):D3),"")"

TOP

PJChen