我對下面的代碼有一個問題 - 它無法將結果生成為“=XXX XXX XXX XXX”（XXX = 數字）有什么問題嗎？
有 3 部分 A 部分：添加評論 <---正確運行 B 部分：將用戶名/編輯者姓名添加到評論中 <---正確運行 C 部分：顯示總和，但形式為“=xxx xxx xxx xxx" not "=Sum(yyy:zzz)" <---沒有錯誤但沒有結果
F 列是總和，第 9 行是專案名稱，范圍 I10 到最后一行，最后一列是表格，但最后一行和最后一列是可變的
Option Explicit
Public Sub AddComments()
Dim ws As Worksheet: Set ws = ActiveSheet 'sheet select
Dim lRow As Long: lRow = ws.Cells(ws.Rows.Count, "F").End(xlUp).Row
Dim lCol As Long: lCol = ws.Cells(9, ws.Columns.Count).End(xlToLeft).Column
Dim srg As Range: Set srg = ws.Range("A1").Resize(lRow, lCol) 'Used area
Dim Data As Variant: Data = srg.Value 'value in used area
Dim r As Long, c As Long, n As Long
Dim Comm As String
Dim fitcomment As Comment
For r = 22 To lRow
For c = 9 To lCol
If Len(Data(r, c)) > 0 Then
n = n 1
Comm = Comm & n & ". " & Data(9, c) & " -" _
& Format(Data(r, c), "$#,##0") & vbLf
End If
Next c
If n > 0 Then
With srg.Cells(r, 6)
.ClearComments
.AddComment Left(Comm, Len(Comm) - 1)
End With
n = 0
Comm = ""
End If
Next r
For Each fitcomment In Application.ActiveSheet.Comments 'Add User Name
fitcomment.Text Text:=Environ$("Username") & vbLf, Start:=1, Overwrite:=False
fitcomment.Shape.TextFrame.Characters(1, Len(Environ$("UserName"))).Font.Bold = True
fitcomment.Shape.TextFrame.AutoSize = True
Next
For r = 22 To lRow
c = 9
Comm = "="
Do
If ws.Cells(r, c).Value <> "" And IsNumeric(ws.Cells(r, c)) And ws.Cells(r, c).Value <> 0 Then
Comm = Comm & " " & ws.Cells(r, c).Value
End If
c = c 1
Loop While Not (c > ws.UsedRange.Columns.Count)
If Comm <> "=" Then
ws.Cells(6, r).Value = Comm
Else
ws.Cells(6, r).Value = ""
End If
Next r
MsgBox "Comments added.", vbInformation
End Sub
uj5u.com熱心網友回復：
看起來您ws.Cells在撰寫Comm結果時已經轉置了行/列引數。
該代碼正在生成一個公式字串，但它被寫入從第 22 列（'V'）開始的第 6 行。單元格 V6 可能超出了您顯示幕上顯示的列 - 這解釋了為什么它看起來沒有輸出。
If Comm <> "=" Then
ws.Cells(6, r).Value = Comm
Else
ws.Cells(6, r).Value = ""
End If
應該：
If Comm <> "=" Then
ws.Cells(r, 6).Value = Comm
Else
ws.Cells(r, 6).Value = ""
End If
順便說一句，String您生成的總和的格式為“= XXX XXX XXX”。Excel 正在為您解決這個問題，使其成為“=XXX XXX XXX”。
轉載請註明出處，本文鏈接：https://www.uj5u.com/qiye/485916.html
上一篇：防止書簽被洗掉