英文:
Identify if activecell offset contains a comment
问题
无法在任何地方找到答案。
我正在尝试运行一个代码，该代码通过一系列的单元格 AL3:AZ201，并且如果其中一个单元格包含 'X'，则查看活动单元格左侧34个单元格并查看它是否与活动单元格左侧17个单元格不同，如果不同，则更改为与活动单元格左侧17个单元格相同的值。
我遇到困难的条件是，我希望代码能够判断如果活动单元格左侧34个单元格包含注释，那么跳过此单元格并转到下一个单元格。
所以举个例子：单元格AO3中有一个 'X'，单元格G3 (0,-34) 和单元格X3 (0,-17) 不相同，所以期望将G3转换为X3的值，但是单元格G3有一个分配给它的注释，所以不应该被覆盖，但令人恼火的是它仍然被覆盖。
出于某种原因，它似乎只是无论注释单元格如何都会被覆盖。任何帮助都将不胜感激：
英文:
can't seem to find the answer anywhere.
I'm trying to run a code that runs through a range of cells AL3:AZ201 and if one of these cells contains and 'X' then look at the cell 34 cells to the left of the active cell and see if it is different to the cell 17 cells to the left of the active cell, if it is then change to the value of the cell 17 cells to the left of the active cell.
The condition I am struggling with is that I want the code to say if the cell 34 cells to the left of the active cell contains a comment, then skip this and go to the next cell.
So as an example: Cell AO3 has an 'X' in it, cell G3 (0,-34) and cell X3 (0,-17) are not the same so would expectantly convert G3 to value X3, however cell G3 has a comment assigned to it so should not be overwritten, but it still does get overwritten annoyingly.
For some reason it just seems to be overwriting regardless of the comment cells. Any help is appreciated:
Sub MoveCellsIfDifferent()
Dim ws As Worksheet
Dim rng As Range
Dim cell As Range
Dim targetCell As Range
' Set the worksheet and range variables
Set ws = ThisWorkbook.Worksheets("Sheet1") ' Replace "Sheet1" with your actual sheet name
Set rng = ws.Range("AL3:AZ201")
' Loop through each cell in the range
For Each cell In rng
' Check if the cell is not blank
If Not IsEmpty(cell.Value) Then
' Check if the cell contains "X"
If cell.Value = "X" Then
' Get the cell 34 cells to the left
Set targetCell = cell.Offset(, -34)
' Check if the cell 34 cells to the left has a comment
If Not targetCell.comment Is Nothing Then
' Skip to the next cell
Exit For
End If
' Check if the cell 34 cells to the left is different from the cell 17 cells to the left
If targetCell.Value <> cell.Offset(, -17).Value Then
' Replace the value of the cell 17 cells to the left with the value of the cell 34 cells to the left
cell.Offset(, -34).Value = cell.Offset(, -17).Value
End If
End If
End If
Next cell
End Sub
答案1
得分: 2
找到了 - 而不是
如果目标单元格.Comment 不为空，则
你需要
如果目标单元格.CommentThreaded 不为空，则
英文:
Found it - instead of
If Not targetCell.Comment Is Nothing Then
You need
If Not targetCell.CommentThreaded Is Nothing Then
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论