For Each loop only works if I activate each cell in range

[troyaaron]

Hi all,

I hope someone can help me...

I have some code which looks at each cell in the selected range, and if the cell value is higher than that of row 3 of the same column, it will add a comment to that cell.

It currently works but only now I have made the loop activate each cell - but I'm sure this shouldn't be necessary. Can anyone see why it doesn't work when i take out the rCell.activate?

Sub ValueToComment()
Application.ScreenUpdating = False
Dim rCell As Range
Dim b As Range

For Each rCell In Selection
rCell.Activate
res = rCell.Offset(0, 1).Text
a = ActiveCell.Row

Set b = rCell.Offset((-a + 3), 0)

With rCell
If rCell.Value < b Then
On Error Resume Next
.Comment.Delete
On Error GoTo 0
.ClearComments
.AddComment
.Comment.Text Text:=res
.Comment.Visible = False
.Comment.Shape.TextFrame.AutoSize = True
Else
On Error Resume Next
.Comment.Delete
On Error GoTo 0
.ClearComments
' .AddComment
' .Comment.Text Text:=res
' .Comment.Visible = False
' .Comment.Shape.TextFrame.AutoSize = True
End If
End With
Next
Set rCell = Nothing
Application.ScreenUpdating = True
End Sub

 

[CharlesChuckieCharles]

Did you have

a = ActiveCell.Row

in the code before you added the activate?

 

[Dustins]

Im not sure if this will help or not, but I think this is because you have "For Each rCell In Selection" in the line above rcell.Activate.

 

[troyaaron]

Hi Both,

@Charles

Yes that was already there...the a = ActiveCell.Row

is so i know how many rows to offset to get back to row 3

@Dustins

Should it be For Each cell in Selection (and not rCell)

 

[Dustins]

ADVERTISEMENT

What is the range of rcell?

 

[Dustins]

Change rcell.Activate to rcell.Select because you cant activate a range and you have this in place.. Dim rcell as Range

 

[CharlesChuckieCharles]

ADVERTISEMENT

Hi Both,

@Charles

Yes that was already there...the a = ActiveCell.Row

is so i know how many rows to offset to get back to row 3

@Dustins

Should it be For Each cell in Selection (and not rCell)

a = rCell.Row

would work perfectly


But what is this trying to do

Set b = rCell.Offset((-a + 3), 0) ??

 

[CharlesChuckieCharles]

Set b = rCell.Offset((-a + 3), 0) ??

it always returns A3 the same cell

 

[RoryA]

Try this:

Sub ValueToComment()
    Dim rCell                      As Range
    Dim res                        As String

    Application.ScreenUpdating = False
    For Each rCell In Selection
        res = rCell.Offset(0, 1).Text

        With rCell
            If rCell.Value < Cells(3, rCell.Column).Value Then
                On Error Resume Next
                .Comment.Delete
                On Error GoTo 0
                .AddComment
                .Comment.Text Text:=res
                .Comment.Visible = False
                .Comment.Shape.TextFrame.AutoSize = True
            Else
                On Error Resume Next
                .Comment.Delete
                On Error GoTo 0
            End If
        End With
    Next
    Set rCell = Nothing
    Application.ScreenUpdating = True
End Sub

 

[CharlesChuckieCharles]

or this

Sub ValueToComment()
Dim rCell As Range
Dim b As Range
For Each rCell In Selection
    Set b = rCell.Offset((-rCell.Row + 3), 0)
    With rCell
        If .Value < b.Value Then
           .ClearComments
           .AddComment (.Offset(0, 1).Text)
          .Comment.Shape.TextFrame.AutoSize = True
        Else
         .ClearComments
        End If
    End With
Next
End Sub