讓公式的值,直接帶入儲存格 - Excel程式區

PJChen

　暱稱: PJ

大學生 Rank: 4

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

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

11^# 跳轉到 »

發表於 2020-1-10 21:32 | 只看該作者

回復 3# kim223824

請問: 我想把類似的方法應用到以下...
W.Sheets("多")的欄數= xcol
Sheets("新")的A4 以 xcol為來源,代入公式,但執行都無作用,請問要怎麼做??

Set Sh = W.Sheets("多")
Sh.Activate
i = "A2:E2"
xcol = Sh.Range(i).Columns.Count '看幾筆資料
With W.Sheets("新")
W.Sheets("新").Activate
Range("A4" & xcol).Value = "=" & "多!A3*多!C3" '公式
Range("A4" & xcol).Value = Range("A4" & xcol).Value
End With

複製代碼

TOP

jcchiang

中學生 Rank: 2

帖子: 234
主題: 19
精華: 0
積分: 276
點名: 0
作業系統: Windows XP
軟體版本: office 2003
閱讀權限: 20
性別: 男
註冊時間: 2013-1-7
最後登錄: 2021-10-7

12^#

發表於 2020-1-13 13:12 | 只看該作者

回復 11# PJChen
加個"."
Set Sh = W.Sheets("多")
      Sh.Activate
      i = "A2:E2"
         xcol = Sh.Range(i).Columns.Count  '看幾筆資料
      With W.Sheets("新")
      W.Sheets("新").Activate
   .Range("A4" & xcol).Value = "=" & "多!A3*多!C3" '公式
   .Range("A4" & xcol).Value = .Range("A4" & xcol).Value

      End With
另外Range("A4" & xcol),如果xcol=5,則為Range("A45")
如果是要Range("A4")加上xcol的欄位則改為
.Range("A4").Resize(1, xcol).Value = "=" & " 多!A3*多!C3" '公式
.Range("A4").Resize(1, xcol).Value = .Range("A4").Resize(1, xcol).Value
如果xcol=5,這樣Range("A4")至Range("E4")都會放入公式,但公式的位置會變化
如果要向下放就將Resize(1,xcol)改為Resize(xcol,1)
以上提供參考

TOP

PJChen

　暱稱: PJ

大學生 Rank: 4

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

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

13^#

發表於 2020-1-13 21:35 | 只看該作者

回復 12# jcchiang
您好,
我把測試檔案附上,方便幫我看一下嗎?它依然不能貼上資料
以往copy資料都是以列數為準,但現在有新的需求,以EX: A:X欄為指定區間,貼資料or向右貼公式
W.Sheets("多")的欄數= xcol
Sheets("新")的A4 以 xcol為來源,代入公式,但執行都無作用,請問要怎麼做??

循欄貼資料.rar (22.02 KB)

TOP

jcchiang

中學生 Rank: 2

帖子: 234
主題: 19
精華: 0
積分: 276
點名: 0
作業系統: Windows XP
軟體版本: office 2003
閱讀權限: 20
性別: 男
註冊時間: 2013-1-7
最後登錄: 2021-10-7

14^#

發表於 2020-1-14 12:47 | 只看該作者

回復 13# PJChen
可以執行阿!!
不是有寫:Range("A4" & xcol),如果xcol=5,則為Range("A45")
以你的程式會在Sheets("新")的Range("A45")有個值
如果要向右貼公式改成這樣:
.Range("A4").Resize(1, xcol).Value = "=" & " 多!A3*多!C3" '公式
.Range("A4").Resize(1, xcol).Value = .Range("A4").Resize(1, xcol).Value
但因為公式並沒有將欄位固定,所以公式會變動
Range("A4")= "=" & " 多!A3*多!C3" '公式
Range("B4")= "=" & " 多!B3*多!D3" '公式
Range("C4")= "=" & " 多!C3*多!E3" '公式
以此類推

TOP

PJChen

　暱稱: PJ

大學生 Rank: 4

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

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

15^#

發表於 2020-1-14 14:49 | 只看該作者

回復 14# jcchiang

感謝再次指導,原來我把Range("A4" & xcol)理解錯誤,
這個.Range("A4").Resize(1, xcol).Value才是我要的結果

TOP

PJChen

　暱稱: PJ

大學生 Rank: 4

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

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

16^#

發表於 2020-1-15 20:46 | 只看該作者

回復 14# jcchiang

不好意思,我在寫公式代入時,因為公式很長,一直出現紅字,是否需要換行?我試著換行,但公式怎麼切都不行,請教這麼長的公式要怎麼換行才可以?

With W.Sheets("優")
W.Sheets("優").Activate
Range("B15:AI19").ClearContents
i = "B15:AI15"
xcol = W.Sheets("優").Range(i).Columns.Count
.Range("B15").Resize(1, xcol).Value = "=" & "VLOOKUP(B$2,飛比!$F:$FO,COUNTA(飛比!$F$3:$FO$3),)"
.Range("B15").Resize(1, xcol).Value = .Range("B15").Resize(1, xcol).Value
.Range("B16").Resize(1, xcol).Value = "=" & "IF(B$2="","",SUMIF(飛比!$F:$F,B$2,飛比!$FT:$FT)+1)"
.Range("B16").Resize(1, xcol).Value = .Range("B16").Resize(1, xcol).Value
.Range("B17").Resize(1, xcol).Value = "=" & "IF(B14-SUMPRODUCT((飛比!$F$4:$F$55=B$2)*(飛比!$B$4:$B$55))>=0,"",ABS(B14-SUMPRODUCT((飛比!$F$4:$F$55=B$2)*(飛比!$B$4:$B$55))))"
.Range("B17").Resize(1, xcol).Value = .Range("B17").Resize(1, xcol).Value
.Range("B18").Resize(1, xcol).Value = "=" & "IF(B$9*SUMPRODUCT((飛比!$F$4:$F$55=B$2)*(飛比!$G$4:$G$55))-SUMPRODUCT((飛比!$F$4:$F$55=B$2)*(飛比!$BJ$3:$CB$3="安")*(飛比!$BJ$4:$CB$55))>=0,"OK",INT(B$9*SUMPRODUCT((飛比!$F$4:$F$55=B$2)*(飛比!$G$4:$G$55))-SUMPRODUCT((飛比!$F$4:$F$55=B$2)*(飛比!$BJ$3:$CB$3="安")*(飛比!$BJ$4:$CB$55))/SUMPRODUCT((飛比!$F$4:$F$55=B$2)*(飛比!$G$4:$G$55))))"
.Range("B18").Resize(1, xcol).Value = .Range("B18").Resize(1, xcol).Value
.Range("B19").Resize(1, xcol).Value = "=" & "IF(B$14*SUMPRODUCT((飛比!$F$4:$F$55=B$2)*(飛比!$G$4:$G$55))-SUMPRODUCT((飛比!$F$4:$F$55=B$2)*(飛比!$BI$4:$BI$55))>=0,"OK",INT(B$14*SUMPRODUCT((飛比!$F$4:$F$55=B$2)*(飛比!$G$4:$G$55))-SUMPRODUCT((飛比!$F$4:$F$55=B$2)*(飛比!$BI$4:$BI$55))/SUMPRODUCT((飛比!$F$4:$F$55=B$2)*(飛比!$G$4:$G$55))))"
.Range("B19").Resize(1, xcol).Value = .Range("B19").Resize(1, xcol).Value
End With

複製代碼

TOP

准提部林

版主 Rank: 7 Rank: 7 Rank: 7

帖子: 2834
主題: 10
精華: 0
積分: 2890
點名: 0
作業系統: 〔略〕
軟體版本: 〔略〕
閱讀權限: 100
性別: 男
來自: 〔略〕
註冊時間: 2013-5-13
最後登錄: 2024-11-22

17^#

發表於 2020-1-16 10:03 | 只看該作者

.Range("B18").Resize(1, xcol) = "=IF(B$9*SUMPRODUCT((飛比!$F$4:$F$55=B$2)*(飛比!$G$4:$G$55))" & _
"-SUMPRODUCT((飛比!$F$4:$F$55=B$2)*(飛比!$BJ$3:$CB$3=""安"")*(飛比!$BJ$4:$CB$55))>=0,""OK""," & _
"INT(B$9*SUMPRODUCT((飛比!$F$4:$F$55=B$2)*(飛比!$G$4:$G$55))-SUMPRODUCT((飛比!$F$4:$F$55=B$2)" & _
"*(飛比!$BJ$3:$CB$3=""安"")*(飛比!$BJ$4:$CB$55))/SUMPRODUCT((飛比!$F$4:$F$55=B$2)*(飛比!$G$4:$G$55))))"

字串連結用 & _
公式中有雙引號的"安", 須外加一對""安""

TOP

PJChen

　暱稱: PJ

大學生 Rank: 4

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

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

18^#

發表於 2020-2-8 20:49 | 只看該作者

回復 17# 准提部林

謝謝准大,
加了引號後,測試沒問題了

TOP

PJChen

　暱稱: PJ

大學生 Rank: 4

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

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

19^#

發表於 2020-2-13 22:15 | 只看該作者

回復 17# 准提部林
准大,
我常用到以下的功能,但工作表內的資料很多,F欄常會間隔幾列,又有其他資料接續,
這時程式就會把間隔列也都填滿
xRow = Cells(Cells.Rows.Count, "F").End(xlUp).Row '看F欄位幾筆資料
Range("JQ4:JQ" & xRow).value = "=AH4-BR4"
Range("JQ4:JQ" & xRow).value = Range("JQ4:JQ" & xRow).value '轉換值
請問
要怎麼讓程式Range("JQ4:JQ" & xRow)以F欄為依據,但F欄有空格時,不要往下執行?(即黃底欄位不要有資料)

TOP

准提部林

版主 Rank: 7 Rank: 7 Rank: 7

帖子: 2834
主題: 10
精華: 0
積分: 2890
點名: 0
作業系統: 〔略〕
軟體版本: 〔略〕
閱讀權限: 100
性別: 男
來自: 〔略〕
註冊時間: 2013-5-13
最後登錄: 2024-11-22

20^#

發表於 2020-2-14 15:22 | 只看該作者

回復 19# PJChen

Range("JQ4:JQ" & xRow).value = "=AH4-BR4"
改
Range("JQ4:JQ" & xRow) = "=IF(F4="""","""",AH4-BR4)"

TOP

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

[收藏此主題] [關注此主題的新回復]

[通過 QQ、MSN 分享給朋友]