返回列表 上一主題 發帖

[發問] 讓公式的值,直接帶入儲存格

[發問] 讓公式的值,直接帶入儲存格

請問大大,

我要讓JQ4計算並得到AH4-BR4的結果,但只要值(不要格式)就好,我是用以下語法,
請問有其他語法,可以直接讓AH4-BR4的值,直接帶入到JQ4一直到如同F欄的列數,
而不要像以下緩慢的步驟,因為檔案中都是公式,早上才run了一下,整個都當機了.
  1.         With Sh
  2.         Sh.Activate
  3.             Range("JQ2") = Range("H1").Value  '前一日庫存總數,貼到AH
  4.             Range("JQ4") = "=" & "AH4-BR4"
  5.             Range("JQ4").Copy
  6.             xRow = Cells(Cells.Rows.Count, "F").End(xlUp).Row  '看F欄位幾筆資料
  7.             Set Rng = Range("JQ4:JQ" & xRow)
  8.             Rng.PasteSpecial Paste:=xlPasteFormulas  '選擇性貼上公式
  9.             Application.CutCopyMode = False '使來源的copy的虛線閃動停止
  10.             Application.DisplayAlerts = False '在程序執行過程中使出現的警告框不顯示
  11.             Application.Calculation = xlAutomatic    '自動計算
  12.             Rng = Rng.Value
  13.         End With
複製代碼

回復 80# 准提部林

謝謝准大
可以正常運作了

TOP

回復 79# PJChen

改下:
If xR = "品名" Then Set xH = xR(2, 7): c = Range("A" & xR.Row + 1).Value: GoTo 101
EXCEL參考資料:
http://blog.xuite.net/smile1000mile/blog

TOP

回復 78# 准提部林

准大,
又來麻煩您了....
程式雖然來回查找,應該說測試了幾天,但沒有一次可以運作成功!就是找不到問題點...
我放上2個訂單,我想這2個可以運作的話,其他的應該就不會有問題了,
函數方面確定無誤,這是每天必做的功課,運行OK.   理貨單_訂購數.rar (373.36 KB)

TOP

回復 77# PJChen

不研究公式是對或錯, 也沒法驗證, 照抄!!! 若有誤自行去修改  

Sub 理貨訂購量()
Dim Rw&, xR As Range, xD, xH As Range, c$, Fx$
Rw = Cells(Rows.Count, "K").End(xlUp).Row
If Rw <= 2 Then Exit Sub
Set xD = CreateObject("Scripting.Dictionary")
xD("全都") = "=SUMIFS(網單.全都!$I:$I,網單.全都!$C:$C,BF理貨!$D2," & _
           "網單.全都!$K:$K,BF理貨!$C2)+IF(BF理貨!$R$283=BF理貨!$B$283,BF理貨!$R2,0)"
xD("統統") = "=SUMIFS(網單.統統!$R:$R,網單.統統!$M:$M,BF理貨!$D2,網單.統統!$AC:$AC,BF理貨!$C2," & _
          "網單.統統!$AE:$AE,BF理貨!$B$1)+IF(BF理貨!$R$1=BF理貨!$B$1,BF理貨!$R2,0)"
xD("德QQK") = "=SUMIF(網單.德QQK!$E:$E,BF理貨!$D2,網單.德QQK!$G:$G)+IF(BF理貨!$R$388=BF理貨!$B$388,BF理貨!$R2,0)"
xD("M社") = "=SUMPRODUCT((網單.M社!$R$2:$R$300=BF理貨!$D2)*(網單.M社!$AP$2:$AP$300))+" & _
           "IF(BF理貨!$R$561=BF理貨!$B$561,BF理貨!$R2,0)"
xD("得來") = "=SUMIFS(網單.得來!$L:$L,網單.得來!$H:$H,BF理貨!$D2,網單.得來!$O:$O,BF理貨!$C2)" & _
           "+IF(BF理貨!$R$420=BF理貨!$B$420,BF理貨!$R2,0)"
xD("W康") = "=SUMPRODUCT((網單.W康!$C$6:$C$298=BF理貨!$D2)*(網單.W康!$D$6:$D$298))+" & _
           "IF(BF理貨!$R$508=BF理貨!$B$508,BF理貨!$R2,0)"

For Each xR In Range("K2:K" & Rw)
    If xR = "品名" Then Set xH = xR(2, 7): c = Range("A" & xR.Row): GoTo 101
    If xR = "合計" Then
       Fx = xD(c)
       If c = "" Or Fx = "" Then GoTo 101
       [Q2].Formula = Fx
       With Range(xH, xR(0, 7)) 'Q欄填入公式
            .FormulaR1C1 = [Q2].FormulaR1C1
            .Value = .Value
            .Replace 0, "", 1  '*****(1,完全符合)
       End With
       c = ""
    End If
101: Next
[Q2] = "訂購數"
End Sub


======================================
EXCEL參考資料:
http://blog.xuite.net/smile1000mile/blog

TOP

本帖最後由 PJChen 於 2020-7-7 19:36 編輯

回復 42# 准提部林
准大好,
我用42樓的程式,修改後用來抓取客戶下單的"訂購數",修改後的程式如下:
  1. Sub 理貨訂購量()
  2. Dim Rw&, xR As Range, xH As Range, c%, Fx$
  3. Rw = Cells(Rows.Count, "K").End(xlUp).Row
  4. If Rw <= 2 Then Exit Sub
  5. '測試其中一個客戶的下單數...全都
  6. [q2] = "=SUMIFS(網單.全都!$I:$I,網單.全都!$C:$C,BF理貨!$D2," & _
  7. "網單.全都!$K:$K,BF理貨!$C2)+IF(BF理貨!$R$283=BF理貨!$B$283,BF理貨!$R2,0)"
  8. For Each xR In Range("K2:K" & Rw)
  9.     If xR = "品名" Then Set xH = xR(2, 7): c = 1: GoTo 101
  10.     If xR = "合計" Then
  11.        If c = 0 Then GoTo 101
  12.        With Range(xH, xR(0, 7)) 'Q欄填入公式
  13.             .FormulaR1C1 = [q2].FormulaR1C1
  14.             .Value = .Value
  15.             .Replace 0, "", 1  '*****(1,完全符合)
  16.        End With
  17.        c = 0
  18.     End If
  19. 101: Next
  20. [q2] = "訂購數"
  21. End Sub
複製代碼
問題如下:   理貨單_訂購數.rar (81.02 KB)
a) Q欄的訂購數,是客戶下單的數量,收到訂單的時間都不是同時的
b) 客戶訂單名稱區分在A欄,客戶的訂單格式都不相同,所以6個客戶有6個公式抓取資料
c) 我修改了之前的一個程式,用來抓取Q欄的訂購數,但程式不是專為這個而設計,所以下一個客戶的訂單,會把前一訂單數給覆蓋
d) R欄的加減數量,是因應客戶有訂單"加量" or "減量"的需求而設,有時客人會在下單幾天前就告知,但不會修改當日訂單,所以需要用到R欄的"加減數量",
可以預先key入,但時間未到時則不予計入!
e) 請問要如何修改程式,可以將A欄名稱(客戶)列入程式中,讓不同時間下單的6個客戶,各自的訂單數不會被覆蓋?
''----------A欄名稱1) 全都
[q2] = "=SUMIFS(網單.全都!$I:$I,網單.全都!$C:$C,BF理貨!$D2," & _
"網單.全都!$K:$K,BF理貨!$C2)+IF(BF理貨!$R$283=BF理貨!$B$283,BF理貨!$R2,0)"
''----------A欄名稱2) 統統
[q2] = "=SUMIFS(網單.統統!$R:$R,網單.統統!$M:$M,BF理貨!$D2,網單.統統!$AC:$AC,BF理貨!$C2," & _
"網單.統統!$AE:$AE,BF理貨!$B$1)+IF(BF理貨!$R$1=BF理貨!$B$1,BF理貨!$R2,0)"
''----------A欄名稱3) 德QQK
'    [q2] = "=SUMIF(網單.德QQK!$E:$E,BF理貨!$D2,網單.德QQK!$G:$G)+IF(BF理貨!$R$388=BF理貨!$B$388,BF理貨!$R2,0)"
'''----------A欄名稱4) M社
'    [q2] = "=SUMPRODUCT((網單.M社!$R$2:$R$300=BF理貨!$D2)*(網單.M社!$AP$2:$AP$300))+IF(BF理貨!$R$561=BF理貨!$B$561,BF理貨!$R2,0)"
'''----------A欄名稱5) 得來
'    [q2] = "=SUMIFS(網單.得來!$L:$L,網單.得來!$H:$H,BF理貨!$D2,網單.得來!$O:$O,BF理貨!$C2)+IF(BF理貨!$R$420=BF理貨!$B$420,BF理貨!$R2,0)"
'''----------A欄名稱6) W康
'    [q2] = "=SUMPRODUCT((網單.W康!$C$6:$C$298=BF理貨!$D2)*(網單.W康!$D$6:$D$298))+IF(BF理貨!$R$508=BF理貨!$B$508,BF理貨!$R2,0)"

TOP

回復 58# jcchiang
我已經解決問題囉...感謝

TOP

本帖最後由 PJChen 於 2020-6-25 22:43 編輯

回復 58# jcchiang

您好,
先說聲謝謝,你寫的程式,總是讓我得到很大的啟發
這段程式,我稍作修改,希望它可以自動對應,增加方便性,
但有些二個問題我無法解決...程式已寫入macro_D    更新理貨單.rar (130.47 KB)
macro_D的"理貨單"工作表,W1 & W2的對應值
x1 = xS.[w1] '對應 活動範圍a
x2 = xS.[w2] '對應檔名
For k = 1 To 7 (原7個檔,先用"下個月理貨單"資料夾的2個檔測試)
xS.[V1] = k
當xS.[w1]=1="暖暖1"
xS.[w2]="暖暖",則打開公用理貨含有"暖暖"字樣的檔案,
將理貨單II的B欄="暖暖1"的儲存格F:P的資料,
copy到"1"工作表的B3貼上值,
使用時發現程式copy資料並不是很快速
For Each a In Range("B:B")
If a = x1 Then d(a.Address) = d(a.Address)

所以我是用理貨單II的F:P區域覆蓋B:L,想使copy一次完成,
然後再將D:F,I:J的公式代入後下拉
現在遇到問題如下:
1) 雖然寫了
For k = 1 To 7
    xS.[V1] = k
但它只會打開第一個檔,我要如何讓它把"下個月理貨單"資料夾,全部檔都依序打開,
然後依k = 1 To 7,所對應的值貼到該貼的地方?

2) D:F,I:J的公式,key入後,希望出現公式,而不是值,例如:林口的檔案
I3值是50,但我希望程式
[i3] = "=" & "Int(" & [m3] & "/" & [L3] & ")" '箱數
[i3] 所得到的答案是公式=INT(M3/L3),
D:F,I:J...4欄也都希望呈現公式而非值

TOP

本帖最後由 PJChen 於 2020-6-25 22:46 編輯

回復 73# 准提部林
感謝准大,
我會再抓時間測試

我們公司規模很小、很摳門,部門內所有檔案都由我設計,
看起來權限好像很大,但很累(這是個沒人想接的工作),
不過我有天馬行空的想像力,連做夢都會夢見excel、
夢見公式的運作,但能否實現,又是另一回事!
大部份的問題我都能自行解決,各位看到我發問,
其實只是冰山一角^.^
不過說到底是能力有限,能拼湊多少算多少囉...

TOP

本帖最後由 准提部林 於 2020-6-25 10:11 編輯

很麻煩的表格~~如果每天都要處理~~很累吧!!!
公式之間有互相引用, 所以上面的方法都會出錯的~~
若用逐格填入~~不會快到哪???

看發帖的檔案有不少版本, 公司應有一定的規模, 為何不請專業的去設計?
時間就是金錢, 除了浪費時間精神體力外, 資料也可能算錯!!!
我們有空也只能稍幫幾許, 但總不能這樣無止境的做, 一切還是要靠自己~~

弄了兩種版本,
1) 字典+陣列版, 寫一半本想放棄, 因為可能(應該是)看不懂程式碼, 給了也沒用吧!! 太複雜~~
Xl0000140(字典+陣列).rar (45.62 KB)

2) 輔助公式版, 能增進多少速度, 沒實測
Xl0000140(輔助公式).rar (56.41 KB)

所有的計算都從上面帖子中的公式用猜的(幾乎每次猜), 依樣畫葫, 自行去修正~~


======================================================
EXCEL參考資料:
http://blog.xuite.net/smile1000mile/blog

TOP

        靜思自在 : 人生不一定球球是好球,但是有歷練的強打者,隨時都可以揮棒。
返回列表 上一主題