- 帖子
- 913
- 主題
- 150
- 精華
- 0
- 積分
- 1089
- 點名
- 0
- 作業系統
- win10
- 軟體版本
- office 2019
- 閱讀權限
- 50
- 性別
- 女
- 註冊時間
- 2011-8-28
- 最後登錄
- 2023-7-19
 
|
2#
發表於 2020-4-19 21:59
| 只看該作者
Dear大大,
請指導 程式2
它可以增加空白註解,但無法將Workbooks("最新庫存B.xlsx").Sheets("飛比")的廠缺值載入註解中,
而且載入的註解無法依 Sheets("飛比")的廠缺值位置載入,註解填滿了 月庫存表.xlsx Range("J4:Y")
且希望註解的框框大小,可以依註解內容自動大小,目前的太大了.- Sub 廠缺註解()
- Dim PH$, FN$, rng As Range, xb As Workbook, Sh As Worksheet, i As String
- Application.ScreenUpdating = False
- Application.DisplayAlerts = False '一般提警示訊息關閉
- Application.Calculation = xlManual '手動計算
- i = Format(Date, "D")
- FN = "月庫存表.xlsx" '目的檔
- PH = "T:\0_自訂表單\其他表單\平日庫表\" '目的
- On Error Resume Next: Set xb = Workbooks(FN): On Error GoTo 0 '檢查檔案是否已開啟(避免當機)
- If xb Is Nothing Then Set xb = Workbooks.Open(PH & FN) '目的若未開啟,則開啟之
- Set Sh = Workbooks("最新庫存B.xlsx").Sheets("飛比")
- With Sh
- xRow = Cells(Cells.Rows.Count, "F").End(xlUp).Row
- Set sRng = Range("BJ4:BY" & xRow) '來源:廠缺範圍
- End With
- With xb.Sheets(i)
- xb.Sheets(i).Activate
- xRow = Cells(Cells.Rows.Count, "E").End(xlUp).Row
- For Each rng In Range("J4:Y" & xRow) '要寫入的範圍,目的訂單範圍
- On Error Resume Next
- If sRng <> "" Then '儲存格的值不為 空白
- Set cmt = rng.Addcomment '將儲存格的值寫入註解中
- cmt.Text "廠缺*" & sRng.Value
- With rng.Comment.Shape.TextFrame.Characters.Font
- .AutoSize = True
- .Name = "細明體"
- .Size = 10
- End With
- End If
- Next rng
- End With
- Application.ScreenUpdating = True '打開屏幕更新
- End Sub
|
|
發表於 2020-4-25 12:01
| 
