Search code examples
excelvbaoffset

Identify if activecell offset contains a comment

I'm trying to run 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:
If the cell 34 cells to the left of the active cell contains a comment, then skip this and go to the next cell.

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 so should not be overwritten.

It is overwriting regardless of the comment cells.
enter image description here

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
Solution

  • Found it - instead of

    If Not targetCell.Comment Is Nothing Then

    You need

    If Not targetCell.CommentThreaded Is Nothing Then