iT邦幫忙

0

VBA列陣公式

  • 分享至 

  • xImage

各位先輩,小弟有點不會將列陣公式轉成VBA
想了很久試了很多次也不行求各位指點一下
還是我理解錯?

Dim myarr As Variant
jrow = Worksheets("calculate").Range("a3").CurrentRegion.Rows.Count
irow = Worksheets("Mandatory holiday").Range("a1").CurrentRegion.Rows.Count

For i2 = 3 To jrow
For i = 2 To irow

 myarr = Worksheets("Mandatory holiday").Range("A2:B11")
 
'列陣公式是這樣的AQ3={SUM(COUNTIFS($G$2:$AK$2,'Mandatory holiday'!$A:$A,$G$3:$AK$3,'Mandatory holiday'!$B:$B))}

Worksheets("Calculate").Range("AQ" & i2) = Application.WorksheetFunction.Sum(Application.WorksheetFunction.CountIfs(Worksheets("calculate").Range("G2:AK2"), myarr(i, 1), Worksheets("calculate").Range("G" & i2 & ":AK" & i2), myarr(i, 2)))

Next
Next i2
End Sub

檔案連結
https://docs.google.com/spreadsheets/d/1iEEednAieuB3Pdh8HpGXfus5L5NQ998EYcwI22KwIlg/edit?usp=sharing

看更多先前的討論...收起先前的討論...
你可以先用錄製巨集的功能,看看EXCEL會怎麼寫的。
blanksoul12 iT邦研究生 5 級 ‧ 2021-10-08 10:01:01 檢舉
你上傳檔案再看吧
AnakinTai iT邦新手 5 級 ‧ 2021-10-08 12:20:16 檢舉
paicheng0111,我試過了.他只是幫我把公式填進去
AnakinTai iT邦新手 5 級 ‧ 2021-10-08 12:27:58 檢舉
blanksoul12 上傳了,我就是在AQ格卡住了
BeEvil_Y iT邦新手 4 級 ‧ 2021-10-08 16:47:33 檢舉
不難,自已去花時間玩,提示:

陣列公式
AQ3={SUM(COUNTIFS($G$2:$AK$2,'Mandatory holiday'!$A:$A,$G$3:$AK$3,'Mandatory holiday'!$B:$B))}

翻譯:
在Excel的儲存格「AQ3」顯示下列資料。

如果G2到AK2總計31格的值,符合Mandatory holiday分頁的A欄資料,計數。
如果G3到AK3總計31格的值,符合Mandatory holiday分頁的A欄資料,計數。
並把上面二欄加起來。


做成VBA(轉譯):
一、計算Mandatory holiday分頁的A欄有多少資料,記進變數。
二、把G2到AK2總計31格的值,記進變數。
三、把G3到AK3總計31格的值,記進變數。
四、把第2、3點的陣列變數拿去比對第1點的變數。符合的話加1。

●宣告陣列變數:
Dim 變數名稱(1 To 100) As Integer '陣列用(記憶體),如果是放字要改string

●計算Mandatory holiday分頁有幾行:
變數名稱 = Worksheets("Mandatory holiday").UsedRange.Rows.Count

●指定儲存格的使用方式:
Worksheets("分頁名稱").Cells(列,欄)

●指定G2到AK2的欄位(G是7,AK是37):
 For 迴圈名稱=7  to 37
  Worksheets("分頁名稱").Cells(2,迴圈名稱)
 next

●判斷是否符合Mandatory holiday分頁的A欄,符合+1:
dim 來源(1 To 100) As Integer
dim 目標(1 To 100) As Integer
dim 計數器=""
計數器=0 '重置

 For 迴圈外=7  to 37
    '每層外迴圈,跑N次內迴圈。
    來源(迴圈外) =Worksheets("分頁名稱").Cells(2,迴圈外)

    For 迴圈內=1  to Mh(最大列數)
     
     目標(迴圈內)=Worksheets("Mandatory holiday").Cells(迴圈內,1)

      if 來源(迴圈外)=目標(迴圈內) then
      計數器=計數器+1
      endif

     MsgBox ("說明:" &來源(迴圈外) &"已比對完。")

    next
 next

●報錯:
MsgBox ("說明" & 變數名稱)

●註解(單引號):
'MsgBox ("說明" & 變數名稱)

●目標
學習「宣告變數」
學習「取得最大列數UsedRange.Rows.Count」
學習「指定儲存格Cells」
學習「指定分頁Worksheet」
學習Msgbox除錯
學習單引號註解給自已看
熟練For迴圈
熟練IF判斷式


你的程式碼解說:

你利用jrow、irow記錄行數
再用內外迴圈,去執行陣列公式(巨集取來的程式)
把結果列在Calculate的AQ欄裡。

我不管怎麼看,都不會把你原本的陣列公式「目地」
改寫成VBA。

如果你卡Range,改用Cells試試好了。
range("C5") 等於 cells(5,3)

如果真的不想摸這些。
你可以嚐試除錯自已的。

Dim myarr As Variant
jrow = Worksheets("calculate").Range("a3").CurrentRegion.Rows.Count
irow = Worksheets("Mandatory holiday").Range("a1").CurrentRegion.Rows.Count

MsgBox ("jrow值:" & jrow)
MsgBox ("irow值:" & irow)

For i2 = 3 To jrow
For i = 2 To irow

myarr = Worksheets("Mandatory holiday").Range("A2:B11")

'列陣公式是這樣的AQ3={SUM(COUNTIFS($G$2:$AK$2,'Mandatory holiday'!$A:$A,$G$3:$AK$3,'Mandatory holiday'!$B:$B))}

dim deBugg = Worksheets("Calculate").Range("AQ" & i2)
MsgBox ("AE格值:" & deBugg)

'這邊我懶得打了,反正你懂我的用意。
dim deBuggCTIFoneO = Worksheets("calculate").Range("G2:AK2")
dim deBuggCTIFoneF = myarr(i, 1)
dim deBuggCTIFtwoO =Worksheets("calculate").Range("G" & i2 & ":AK" & i2)
dim deBuggCTIFtwoF =myarr(i, 2)
MsgBox ("比對值:" & "來源一" &deBuggCTIFoneO & "陣一" & deBuggCTIFoneF & "來源二"& deBuggCTIFtwoO & "陣二" & deBuggCTIFtwoF )

Worksheets("Calculate").Range("AQ" & i2) = Application.WorksheetFunction.Sum(Application.WorksheetFunction.CountIfs(Worksheets("calculate").Range("G2:AK2"), myarr(i, 1), Worksheets("calculate").Range("G" & i2 & ":AK" & i2), myarr(i, 2)))

Next
Next i2
End Sub

自已試啊,因為你又不給資料。
不給,就代表不能給。

我們只能給方向,你自已去Try。

玩Excel.vba,用Msgbox()除錯最快。
很快就能自已抓到問題點。

用Msgbox()也有一個好處,就是避免死當。
因為他還要秀出來,等你按確定。

當你發現按確定會死當(因為會秀出值)
你不按確定,強制中斷程式時,就不會死當了。

很多人,都是因為沒存檔,死當。
程式碼全部重打。

第一次RUN,你就要整串貼進txt檔,等他死當。
後面再用msgbox()除錯。
挫折感就會很小。
AnakinTai iT邦新手 5 級 ‧ 2021-10-09 19:05:20 檢舉
BeEvil_Y 大大
非常感謝你,你的解釋非常清楚,也給了我很重要的指引,
小弟的VBA是自學很多地方還是不會.特別是在最後的死檔問題.
以前試過無限LOOP.直到死機.所以有碼要重打.

我已經解決了這個問題.以下是我最後得出的結果
我利用了3個迴圈來完成.

```
irow = Worksheets("calculate").Range("a3").CurrentRegion.Rows.Count
jrow = Worksheets("Mandatory holiday").Range("a1").CurrentRegion.Rows.Count
Dim A As Variant
Dim B As Variant


For i = 3 To irow
For i2 = 7 To 37
For j = 2 To jrow
A = Worksheets("Calculate").Cells(i, i2)
B = Worksheets("Mandatory holiday").Range("B" & j)
If A = B Then
k = k + 1
Worksheets("Calculate").Range("AQ" & i) = k
End If
Next j
Next i2
k = 0
Next i

End Sub
```
圖片
  直播研討會
圖片
{{ item.channelVendor }} {{ item.webinarstarted }} |
{{ formatDate(item.duration) }}
直播中

尚未有邦友回答

立即登入回答