- 帖子
- 5923
- 主題
- 13
- 精華
- 1
- 積分
- 5986
- 點名
- 0
- 作業系統
- win10
- 軟體版本
- Office 2010
- 閱讀權限
- 150
- 性別
- 男
- 來自
- 台灣基隆
- 註冊時間
- 2010-5-1
- 最後登錄
- 2022-1-23
        
|
回復 12# PJChen - Option Explicit
- Sub 廠缺Text()
- Dim Rng As Range, M As Variant, W As Workbook, Sh As Worksheet, i As Integer
- Set W = Workbooks("最新庫存.xlsx")
- Set Sh = W.Sheets("廠缺表")
- '** 你檔案的資料不適用 'Range.CurrentRegion 屬性 傳回 Range 物件，該物件代表目前的區域。
- ** '目前區域是指以任意空白列及空白欄的組合為邊界的範圍
-
- With Sh.Range("aw1").CurrentRegion
- Set Rng = Range(.Rows(2), .Rows(.Rows.Count)) '來源區域 不讀取第1列,設定從第2列開始讀取
- Rng.Cells(1, 5).Select
- MsgBox "你檔案不適用 CurrentRegionRng" & vbLf & Rng.Address(, , , 1, 1) & vbLf & Selection.Address(0, 0) & "錯誤值 造成程錯誤"
- End With
- M = "廠缺Text" '設定M為接收資料的變數,表頭為""內的文字,可任意更改
- Set Rng = Sh.Range("aw2")
- Do Until Rng.Text = "" Or Rng.Text = 0 Or IsError(Rng)
- i = 1
- Do While Rng(i).Text <> "" And Rng(i).Text <> 0 And Not IsError(Rng(i))
- M = M & "," & Rng(i) '第i個處存格
- i = i + 1
- Loop
- Set Rng = Rng.Offset(, 1) '**左移一欄
- Loop
- M = Split(M, ",")
- With Sh.Range("bm:bm") '將讀取資料,顯示到BM欄
- .Clear
- .Resize(UBound(M) + 1) = Application.WorksheetFunction.Transpose(M)
- End With
- End Sub
|
|
發表於 2019-9-16 11:32
| 
