- 帖子
- 522
- 主題
- 36
- 精華
- 1
- 積分
- 603
- 點名
- 0
- 作業系統
- win xp sp3
- 軟體版本
- Office 2003
- 閱讀權限
- 50
- 性別
- 男
- 註冊時間
- 2012-12-13
- 最後登錄
- 2021-7-11
|
5#
發表於 2015-12-6 13:03
| 只看該作者
可以用VBA嗎?
試試看:- Public arW, arL
- '取得寛度與長度界限的陣列, 供Match用
- Sub init()
- ReDim arW(3) As Integer
- ReDim arL(3, 3) As Integer
- Dim W1 As Integer, L1 As Integer
- arW(0) = Split(Cells(3, 3), "~")(1) '寛度的上限
- For W1 = 0 To 2
- arW(W1 + 1) = Split(Cells(W1 * 3 + 3, 3), "~")(0) '寛度按降冪排
- For L1 = 0 To 2
- arL(W1, L1) = Split(Cells(W1 * 3 + L1 + 3, 5), "~")(0) '長度按升冪排
- Next
- arL(W1, L1) = Split(Cells(W1 * 3 + L1 + 2, 5), "~")(1) '長度的上限
- Next
- End Sub
- Private Sub CommandButton1_Click()
- Dim I As Integer, J As Integer, arL2(3) As Integer
- Dim MHW, MHL, IDW As String, IDL As String
- init
- For I = 4 To [G4].End(xlDown).Row
- MHW = Application.Match(Cells(I, 8), arW, -1)
- IDW = Application.Index([B1:B11], MHW * 3, 1)
- For J = 0 To 3
- arL2(J) = arL(MHW - 1, J) '將2維陣列轉為1維陣列
- Next
- MHL = Application.Match(Cells(I, 9), arL2, 1)
- '長度代號分[D3:D5,D6:D9,D10:D12]三區
- IDL = Application.Index([D3:D5,D6:D8,D9:D11], MHL, 1, MHW)
- Cells(I, 10) = IDW & "_" & IDL
- Next
- End Sub
|
|
發表於 2015-12-6 10:20
| 
