12
返回列表 上一主題 發帖

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

yen956

帖子
522 
主題
36 
精華
1 
積分
603 
點名
0  
作業系統
win xp sp3 
軟體版本
Office 2003 
閱讀權限
50 
性別
男 
註冊時間
2012-12-13 
最後登錄
2021-7-11 
1# 跳轉到 » 倒序看帖
打印
tT
發表於 2015-12-6 13:15 | 只看該作者

[發問] 如何在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

  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)
收藏 分享

yen956

帖子
522 
主題
36 
精華
1 
積分
603 
點名
0  
作業系統
win xp sp3 
軟體版本
Office 2003 
閱讀權限
50 
性別
男 
註冊時間
2012-12-13 
最後登錄
2021-7-11 
2#
發表於 2015-12-6 13:41 | 只看該作者
Sorry, 應該放在程式區才對, 只好將錯就錯!!

TOP

准提部林

帖子
2798 
主題
10 
精華
0 
積分
2854 
點名
0  
作業系統
〔略〕 
軟體版本
〔略〕 
閱讀權限
100 
性別
男 
來自
〔略〕 
註冊時間
2013-5-13 
最後登錄
2024-4-15 
3#
發表於 2015-12-6 18:44 | 只看該作者
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)

TOP

yen956

帖子
522 
主題
36 
精華
1 
積分
603 
點名
0  
作業系統
win xp sp3 
軟體版本
Office 2003 
閱讀權限
50 
性別
男 
註冊時間
2012-12-13 
最後登錄
2021-7-11 
4#
發表於 2015-12-6 19:52 | 只看該作者
回復 3# 准提部林
謝謝准大, 非常好用又精簡的VBA,
正在用剛學來的監看視窗研究中,
對於如何使用2維陣列中的某一陣列的表達式
也在大大的這個VBA中得到解答, 謝謝!!

TOP

yen956

帖子
522 
主題
36 
精華
1 
積分
603 
點名
0  
作業系統
win xp sp3 
軟體版本
Office 2003 
閱讀權限
50 
性別
男 
註冊時間
2012-12-13 
最後登錄
2021-7-11 
5#
發表於 2015-12-7 10:25 | 只看該作者
本帖最後由 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

stillfish00

帖子
1018 
主題
15 
精華
0 
積分
1058 
點名
0  
作業系統
win7 32bit 
軟體版本
Office 2016 64-bit 
閱讀權限
50 
性別
男 
來自
桃園 
註冊時間
2012-5-9 
最後登錄
2022-9-28 
6#
發表於 2015-12-7 17:05 | 只看該作者
回復 5# yen956

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


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

TOP

c_c_lai

帖子
2035 
主題
24 
精華
0 
積分
2031 
點名
0  
作業系統
Win7 
軟體版本
Office2010 
閱讀權限
100 
性別
男 
註冊時間
2012-3-22 
最後登錄
2024-2-1 
7#
發表於 2015-12-8 08:54 | 只看該作者
回復 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

  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
複製代碼

TOP

c_c_lai

帖子
2035 
主題
24 
精華
0 
積分
2031 
點名
0  
作業系統
Win7 
軟體版本
Office2010 
閱讀權限
100 
性別
男 
註冊時間
2012-3-22 
最後登錄
2024-2-1 
8#
發表於 2015-12-8 10:21 | 只看該作者
本帖最後由 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

准提部林

帖子
2798 
主題
10 
精華
0 
積分
2854 
點名
0  
作業系統
〔略〕 
軟體版本
〔略〕 
閱讀權限
100 
性別
男 
來自
〔略〕 
註冊時間
2013-5-13 
最後登錄
2024-4-15 
9#
發表於 2015-12-8 11:31 | 只看該作者
回復 8# c_c_lai


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

TOP

c_c_lai

帖子
2035 
主題
24 
精華
0 
積分
2031 
點名
0  
作業系統
Win7 
軟體版本
Office2010 
閱讀權限
100 
性別
男 
註冊時間
2012-3-22 
最後登錄
2024-2-1 
10#
發表於 2015-12-8 13:13 | 只看該作者
本帖最後由 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

  5. Function AddCol2(w, h, i) As String  '  故意帶入 w 參數
  6.     AddCol2 = h + i
  7. 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

        靜思自在 : 君子立恆志，小人恆立志。
12
返回列表 上一主題