- 帖子
- 5923
- 主題
- 13
- 精華
- 1
- 積分
- 5986
- 點名
- 0
- 作業系統
- win10
- 軟體版本
- Office 2010
- 閱讀權限
- 150
- 性別
- 男
- 來自
- 台灣基隆
- 註冊時間
- 2010-5-1
- 最後登錄
- 2022-1-23
        
|
回復 6# yangjie
UBound(AR) 陣列第1維的元素上限索引值,
UBound(AR, 2) 陣列第2維的元素上限索引值- Option Explicit
- Sub EX()
- Dim d As Object, R As Range, S As String, AR(), i As Integer, ii As Integer
- Set d = CreateObject("scripting.dictionary") '設立字典物件
- If Application.CountA(Sheets("Sheet3").Cells) = 0 Then
- '3.Sheets("Sheet3").[a1].Resize(i + 1, UBound(AR, 2)) = AR 如何修改為最後一列
- ReDim Preserve AR(i)
- AR(i) = Sheets("100多筆").UsedRange.Cells(1).Resize(, 12).Value
- i = i + 1
- End If
- '1.因為 欄位有21欄但判讀相異準則1~12欄 copy是21欄,dictionary 應如何寫
- For Each R In Sheets("100多筆").UsedRange.Columns(1).Resize(, 12).Rows
- S = Join(Application.Transpose(Application.Transpose(R.Value)), ",")
- '是否有更好之方式? 若是判讀相異準則為第一第四第九第十欄
- 'S = R.Cells(1, 1) & R.Cells(1, 4) & R.Cells(1, 9) & R.Cells(1, 10)
- 'S = R(1, 1) & R(1, 4) & R(1, 9) & R(1, 10)
- d(S) = ""
- Next
- For Each R In Sheets("1000多筆").UsedRange.Columns(1).Resize(, 12).Rows
- S = Join(Application.Transpose(Application.Transpose(R.Value)), ",")
- 'S = R.Cells(1, 1) & R.Cells(1, 4) & R.Cells(1, 9) & R.Cells(1, 10)
-
- If d.exists(S) = False Then '字典物件的Key不存在
- 'If d(1).exists(S) = False Then '字典物件的Key不存在
- '2 其中d(1)是甚麼? 我的筆誤,原本d要設為陣列,
- ReDim Preserve AR(i)
- AR(i) = R.Value
- i = i + 1
- End If
- Next
- AR = Application.Transpose(Application.Transpose(AR))
- 'Sheets("Sheet3").[a1].Resize(i + 1, UBound(AR, 2)) = AR
- '3.Sheets("Sheet3").[a1].Resize(i + 1, UBound(AR, 2)) = AR 如何修改為最後一列
- With Sheets("Sheet3")
- .Cells(.Rows.Count, "a").End(xlUp).Offset(Abs(Application.CountA(.Range("A:A")) > 1)).Resize(i, UBound(AR, 2)) = AR
- End With
- End Sub
如圖
|
|
發表於 2015-4-3 14:37
| 
