我对 VBA 真的很陌生(昨天决定尝试开始深入学习它),所以我给自己设定了一个小项目。
我找到了这段代码(从这里),并试图通过它进行逻辑处理,但我被困住了 - 我想把它指向当前选定的范围,而不是循环遍历所有工作表和这些工作表中的所有已用单元格。当我尝试将"iCell"定义为当前选择时,我不断收到类型错误。
Sub CommentTheHeckOuttaIt()
Dim WS As Worksheet
Dim iCell As Range
For Each WS In ActiveWorkbook.Worksheets
For Each iCell In WS.UsedRange
With iCell
If CStr(.Value) <> "" Then
.ClearComments
.AddComment
.Comment.Visible = False
.Comment.Text Text:=CStr(.Value)
.Comment.Shape.ScaleWidth 5.87, msoFalse, msoScaleFromTopLeft
.Comment.Shape.ScaleHeight 2.26, msoFalse, msoScaleFromTopLeft
End If
If .Formula <> "" Then
.ClearComments
.AddComment
.Comment.Visible = False
.Comment.Text Text:=CStr(.Formula)
.Comment.Shape.ScaleWidth 5.87, msoFalse, msoScaleFromTopLeft
.Comment.Shape.ScaleHeight 2.26, msoFalse, msoScaleFromTopLeft
End If
End With
Next
Next
End Sub
Sub AllCommentsMustDIE()
Dim WS As Worksheet
Dim iCell As Range
For Each WS In ActiveWorkbook.Worksheets
For Each iCell In WS.UsedRange
iCell.ClearComments
Next
Next
End Sub
如果您知道与这里相关的参考资料,我很高兴阅读,而不一定是找人为我解决它的人。我只是不知道如何措辞搜索。
您需要删除外部 for 循环才能不对所有工作表执行此操作。 然后,您需要遍历所选范围;比如下面。 我会让你修复第二个子。
Sub CommentTheHeckOuttaIt()
Dim iCell As Range
For Each iCell In Selection
With iCell
If CStr(.Value) <> "" Then
.ClearComments
.AddComment
.Comment.Visible = False
.Comment.Text Text:=CStr(.Value)
.Comment.Shape.ScaleWidth 5.87, msoFalse, msoScaleFromTopLeft
.Comment.Shape.ScaleHeight 2.26, msoFalse, msoScaleFromTopLeft
End If
If .Formula <> "" Then
.ClearComments
.AddComment
.Comment.Visible = False
.Comment.Text Text:=CStr(.Formula)
.Comment.Shape.ScaleWidth 5.87, msoFalse, msoScaleFromTopLeft
.Comment.Shape.ScaleHeight 2.26, msoFalse, msoScaleFromTopLeft
End If
End With
Next
End Sub