- 帖子
- 5923
- 主題
- 13
- 精華
- 1
- 積分
- 5986
- 點名
- 0
- 作業系統
- win10
- 軟體版本
- Office 2010
- 閱讀權限
- 150
- 性別
- 男
- 來自
- 台灣基隆
- 註冊時間
- 2010-5-1
- 最後登錄
- 2022-1-23
        
|
15#
發表於 2012-1-17 13:14
| 只看該作者
回復 14# dafa
陣列字元太多沒辦法全部塞入清單內- Sub 清單明細()
- Dim bb, kk(), aa, B As Integer, Rng As Range
- 'kk() 動態陣列 可依需求指定元素的數量 , kk(30) :有31個元素 (0 To 30 )
- If ActiveCell.Cells.Column <> "4" Then Exit Sub
- aa = ActiveCell
- If aa = "Account Code " Then Exit Sub
- If aa = "" Then Exit Sub
- g = Worksheets("清單明細").Cells(65536, 2).End(xlUp).Row
- B = 0 '動態陣列的數目: 此時 動態陣列尚未指定元素數目
- For i = 1 To g
- e = Worksheets("清單明細").Cells(i, 2)
- If Worksheets("清單明細").Cells(i, 2) = aa Then
- j = Worksheets("清單明細").Cells(i, 34).End(xlToLeft).Column
- For a = 3 To j
- ReDim Preserve kk(B) 'ReDim 陳述式 在程序層次中用來重新配置動態陣列變數的儲存空間。
- 'Preserve 選擇性引數。當改變原有陣列最後一維的大小時，仍然保有原來的資料的關鍵字。
- kk(B) = Worksheets("清單明細").Cells(i, a)
- B = B + 1
- Next a
- bb = Join(kk, ",")
- Exit For
- End If
- Next i
- pp = Worksheets("清單明細").Cells(i, 3)
- MsgBox pp, , "Account_Code說明"
- Set Rng = activecells
- ActiveCell.Cells(, 4).Select
- ActiveCell = ""
- With Rng.Validation
- .Delete
- .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
- xlBetween, Formula1:=bb
- .IgnoreBlank = True
- .InCellDropdown = True
- .InputTitle = ""
- .ErrorTitle = ""
- .InputMessage = ""
- .ErrorMessage = "You must enter a number from five to ten"
- .ShowInput = True
- .ShowError = True
- End With
- 'Set kk = Nothing 'kk 是陣列 不是物件
- ActiveCell.Offset(, -5) = Date
- ActiveCell.Offset(, 1) = Environ("UserName")
- End Sub
|
|
發表於 2012-1-16 22:50
| 
