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

yen956

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

下載次數: 14

2015-12-6 13:14

yen956

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

准提部林

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

＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝
另一參考，自訂函數，彈性較好些：
Ｊ４．公式：=get_wl(B$3:E$11,H4,I4)

yen956

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

yen956

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

下載 (31.74 KB)

2015-12-7 10:26

下載 (8.54 KB)

2015-12-7 10:27

stillfish00

回復 5# yen956

下載 (33.47 KB)

2015-12-7 16:53

Index 函數可以忽略第三參數，只取出某 row 成為一維陣列
但因原本該函數是設計給工作表 Range 用 , 故須注意 :
1. 二維陣列 row 數不可超過65536
2. 取出的一維陣列，index是由1開始而不是0
3. 二維陣列過大時，該函數效率很差。

c_c_lai

回復 5# yen956
我將准提部林版大的自訂函數稍稍修改了一些，
你用 [F8] 去 Debug 一次，便會全盤明瞭了。
我將結果改寫到 K 欄，以便你進行比對寫入值。

1. '  自訂函數，彈性較好些：  By 准提部林
   
2. '  Ｊ４．公式：=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. 
    
19. Sub Ex()
    
20.     Dim cts As Integer
    
21.    
    
22.     For cts = 0 To 5
    
23.        [K4].Offset(cts) = GET_WL(Range("B$3:E$11"), [H4].Offset(cts), [I4].Offset(cts))
    
24.     Next cts
    
25. End Sub

複製代碼

c_c_lai

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

下載 (14.19 KB)

2015-12-8 10:24

准提部林

回復 8# c_c_lai


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

c_c_lai

回復 9# 准提部林
謝謝您回覆，我使用您#3樓的程式
測的手都軟了，還是一樣 (無論是在任何欄位，包含J4)
依然是 #REF!。甚至寫了兩支測試程式，都 PASS：

1. Function AddCol(h, i) As String
   
2.     AddCol = h + i
   
3. End Function
   
4. 
   
5. Function AddCol2(w, h, i) As String  '  故意帶入 w 參數
   
6.     AddCol2 = h + i
   
7. End Function

複製代碼

下載 (35.63 KB)

2015-12-8 13:09

唯獨 =get_wl(B$3:E$11,H4,J4) 不行：

下載 (13.87 KB)

2015-12-8 13:12

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