PJChen

回復 3# kim223824

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

1.    Set Sh = W.Sheets("多")
   
2.         Sh.Activate
   
3.         i = "A2:E2"
   
4.             xcol = Sh.Range(i).Columns.Count  '看幾筆資料
   
5.         With W.Sheets("新")
   
6.         W.Sheets("新").Activate
   
7.         Range("A4" & xcol).Value = "=" & "多!A3*多!C3" '公式
   
8.        Range("A4" & xcol).Value = Range("A4" & xcol).Value
   
9.         
   
10.         End With   

複製代碼

jcchiang

回復 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)
以上提供參考

PJChen

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

下載次數: 9

2020-1-13 21:33

jcchiang

回復 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" '公式
以此類推

PJChen

回復 14# jcchiang

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

PJChen

回復 14# jcchiang

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

1.         With W.Sheets("優")
   
2.             W.Sheets("優").Activate
   
3.                 Range("B15:AI19").ClearContents
   
4.                     i = "B15:AI15"
   
5.                         xcol = W.Sheets("優").Range(i).Columns.Count
   
6.                             .Range("B15").Resize(1, xcol).Value = "=" & "VLOOKUP(B$2,飛比!$F:$FO,COUNTA(飛比!$F$3:$FO$3),)"
   
7.                             .Range("B15").Resize(1, xcol).Value = .Range("B15").Resize(1, xcol).Value
   
8.                             .Range("B16").Resize(1, xcol).Value = "=" & "IF(B$2="","",SUMIF(飛比!$F:$F,B$2,飛比!$FT:$FT)+1)"
   
9.                             .Range("B16").Resize(1, xcol).Value = .Range("B16").Resize(1, xcol).Value
   
10.                             .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))))"
    
11.                             .Range("B17").Resize(1, xcol).Value = .Range("B17").Resize(1, xcol).Value
    
12.                             .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))))"
    
13.                             .Range("B18").Resize(1, xcol).Value = .Range("B18").Resize(1, xcol).Value
    
14.                             .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))))"
    
15.                             .Range("B19").Resize(1, xcol).Value = .Range("B19").Resize(1, xcol).Value
    
16.         End With

複製代碼

准提部林

.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))))"

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

PJChen

回復 17# 准提部林

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

PJChen

回復 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欄有空格時,不要往下執行?(即 黃底欄位不要有資料)

下載 (60.28 KB)

2020-2-13 22:14

准提部林

回復 19# PJChen


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