返回列表 上一主題 發帖

[發問] 如何在match中引用2維陣列

[發問] 如何在match中引用2維陣列

本帖最後由 yen956 於 2015-12-6 13:16 編輯

下列VBA中,有一段match須引用1維陣列,
故先將2維陣列先轉成1維陣列再引用(紅色部分),
請問紅色部分如何正確引用(如何精簡)?
  1. Public arW, arL
  2. '取得寛度與長度界限的陣列, 供Match用
  3. Sub init()
  4.     ReDim arW(3) As Integer
  5.     ReDim arL(3, 3) As Integer
  6.     Dim W1 As Integer, L1 As Integer
  7.     arW(0) = Split(Cells(3, 3), "~")(1)    '寛度的上限
  8.     For W1 = 0 To 2
  9.         arW(W1 + 1) = Split(Cells(W1 * 3 + 3, 3), "~")(0) '寛度按降冪排
  10.         For L1 = 0 To 2
  11.             arL(W1, L1) = Split(Cells(W1 * 3 + L1 + 3, 5), "~")(0)   '長度按升冪排
  12.         Next
  13.         arL(W1, L1) = Split(Cells(W1 * 3 + L1 + 2, 5), "~")(1)     '長度的上限
  14.     Next
  15. End Sub

  16. Private Sub CommandButton1_Click()
  17.     Dim I As Integer, J As Integer, arL2(3) As Integer
  18.     Dim MHW, MHL, IDW As String, IDL As String
  19.     init
  20.     For I = 4 To [G4].End(xlDown).Row
  21.         MHW = Application.Match(Cells(I, 8), arW, -1)
  22.         IDW = Application.Index([B1:B11], MHW * 3, 1)
  23. [color=Red]        For J = 0 To 3
  24.             arL2(J) = arL(MHW - 1, J)    '將2維陣列轉為1維陣列
  25.         Next
  26.         MHL = Application.Match(Cells(I, 9), arL2, 1)[/color]
  27.         '長度代號分[D3:D5,D6:D9,D10:D12]三區
  28.         IDL = Application.Index([D3:D5,D6:D8,D9:D11], MHL, 1, MHW)
  29.         Cells(I, 10) = IDW & "_" & IDL
  30.     Next
  31. End Sub
複製代碼
(註:VBA可正常運作)
       For J = 0 To 3
            arL2(J) = arL(MHW - 1, J)    '將2維陣列轉為1維陣列
        Next
        MHL = Application.Match(Cells(I, 9), arL2, 1)

多條件判斷編製名稱1206.rar (11.7 KB)

Sorry, 應該放在程式區才對, 只好將錯就錯!!

TOP

Function GET_WL(xA, Va, Vb) As String
Dim Arr, AA, j&, Jm%, T$
Arr = xA.Value
For j = 1 To UBound(Arr)
  If Arr(j, 2) <> "" Then
    Jm = 0:  AA = Split(Arr(j, 2) & "~", "~")
    If Va > Val(AA(0)) And Va <= Val(AA(1)) Then T = Arr(j, 1): Jm = 1
  End If
  If Jm = 1 Then
    AA = Split(Arr(j, 4) & "~", "~")
    If Vb > Val(AA(0)) And Vb <= Val(AA(1)) Then GET_WL = T & "_" & Arr(j, 3): Exit For
  End If
Next j
End Function

=============================
另一參考,自訂函數,彈性較好些:
J4.公式:=get_wl(B$3:E$11,H4,I4)

TOP

回復 3# 准提部林
謝謝准大, 非常好用又精簡的VBA,
正在用剛學來的監看視窗研究中,
對於如何使用2維陣列中的某一陣列的表達式
也在大大的這個VBA中得到解答, 謝謝!!

TOP

本帖最後由 yen956 於 2015-12-7 10:27 編輯

自言自語:
雖然在監看視窗中, 可以看得到二維陣列arL()
的一維表示式:arL(0),arL(1),..
但在VBA中郤不能使用arL(0),arL(1),..的表示式,
(會出現索引超出範圍的錯誤!)
故,二維陣列若要給match引用,還是要轉成一維陣列,
似無法精簡.
(當然, 准大的VBA非當精簡,又提供另一思路, 真的很不錯)
陣列問題.jpg
2015-12-7 10:26

陣列索引.jpg
2015-12-7 10:27

TOP

回復 5# yen956

擷取.JPG
2015-12-7 16:53


Index 函數可以忽略第三參數,只取出某 row 成為一維陣列
但因原本該函數是設計給工作表 Range 用 , 故須注意 :
1. 二維陣列 row 數不可超過65536
2. 取出的一維陣列,index是由1開始而不是0
3. 二維陣列過大時,該函數效率很差。
表達不清、題意不明確、沒附檔案格式、沒有討論問題的態度~~~~~~以上愛莫能助。

TOP

回復 5# yen956
我將准提部林版大的自訂函數稍稍修改了一些,
你用 [F8] 去 Debug 一次,便會全盤明瞭了。
我將結果改寫到 K 欄,以便你進行比對寫入值。
  1. '  自訂函數,彈性較好些:  By 准提部林
  2. '  J4.公式:=get_wl(B$3:E$11,H4,I4)
  3. Function GET_WL(xA As Variant, Va As Range, Vb As Range) As String
  4.     Dim Arr, AA, j&, Jm%, T$
  5.    
  6.     Arr = xA
  7.     For j = 1 To UBound(Arr)
  8.         If Arr(j, 2) <> "" Then
  9.             Jm = 0:  AA = Split(Arr(j, 2) & "~", "~")
  10.             If Va > Val(AA(0)) And Va <= Val(AA(1)) Then T = Arr(j, 1): Jm = 1
  11.         End If
  12.         If Jm = 1 Then
  13.             AA = Split(Arr(j, 4) & "~", "~")
  14.             If Vb > Val(AA(0)) And Vb <= Val(AA(1)) Then GET_WL = T & "_" & Arr(j, 3): Exit For
  15.         End If
  16.     Next j
  17. End Function

  18. Sub Ex()
  19.     Dim cts As Integer
  20.    
  21.     For cts = 0 To 5
  22.        [K4].Offset(cts) = GET_WL(Range("B$3:E$11"), [H4].Offset(cts), [I4].Offset(cts))
  23.     Next cts
  24. End Sub
複製代碼

TOP

本帖最後由 c_c_lai 於 2015-12-8 10:24 編輯

回復 3# 准提部林
請教版大,
請問我在K11欄內輸入  =get_wl(B$3:E$11,H4,I4)
為何會出現 #REF! 字樣?
自訂函數在 Excel 要如何設定其定義讓它能被引用?
P.S. 真不好意思我對 Excel  如何使用內在函數,以及
       自訂函數 (Function) 如何在欄內應用方面所知有限。
謝謝您指教!
F.png
2015-12-8 10:24

TOP

回復 8# c_c_lai


#REF與儲存格參照有關, 應與自訂函數無關, 若K4也是同公式卻可正常顯示, 就要查看看原因?
自訂函數大致與一般公式類似, 只是提供參數或儲存範圍讓程式去運算~~

TOP

本帖最後由 c_c_lai 於 2015-12-8 13:21 編輯

回復 9# 准提部林
謝謝您回覆,我使用您#3樓的程式
測的手都軟了,還是一樣 (無論是在任何欄位,包含J4)
依然是 #REF!。甚至寫了兩支測試程式,都 PASS:
  1. Function AddCol(h, i) As String
  2.     AddCol = h + i
  3. End Function

  4. Function AddCol2(w, h, i) As String  '  故意帶入 w 參數
  5.     AddCol2 = h + i
  6. End Function
複製代碼
F.png
2015-12-8 13:09

唯獨 =get_wl(B$3:E$11,H4,J4) 不行:
F.png
2015-12-8 13:12

問題應該出在程式碼內部無法參照到 B$3:E$11,
但用 VBA 檢測也找不出問題點。

TOP

        靜思自在 : 【生命在呼吸間】佛陀說:「生命在呼吸間。」人無法管住自己的生命,更無法擋住死期,讓自己永住人間。既然生命去來這麼無常,我們更應該好好地愛惜它、利用它、充實它,讓這無常、寶貴的生命,散發它真善美的光輝,映照出生命真正的價值。
返回列表 上一主題