Comment Cell When Row With Consecutive Column Has Same Entry

ceruin · Jun 21st 2007

Can anyone suggest a formula to automatically comment a cell when rows with given number consecutive columns has same entry example:

A B C D E F G H
1 0 0 0 0 5 6 7 18
2 0 0 5 6 7 0 8 26
3 0 5 0 0 0 0 0 5

row 1: there are minimum 4 '0' in consecutive columns, so H1 is sum of A1:G1 = 18 and automatically comment cell H1 "there are 4 zero"

row 2: because there are no 4 '0' in consecutive columns, H2 is 26 , and no comment

row 3: there are 5 '0' in consecutive columns, so H3 is 5 and automatically commented cell H3 "there are 5 zero"

p45cal · Jun 21st 2007

Re: Automatically Comment Cell When Row With Consecutive Column Has Same Entry

I think a formula might be very complicated, and I think impossible as far as writing a comment is concerned. So I've gone down the vba route. Explore this (albeit amateur) offering:

Code

Sub blah()
For rw = 1 To 3 'to process rows 1 to 3
FoundNoOfConsecutives = 0
FoundConsecutiveVal = -1
noOfConsecutives = 1
ConsecutiveVal = -1
For Each cll In Range("B" & rw & ":G" & rw).Cells
If cll.Value = cll.Offset(0, -1).Value Then
    noOfConsecutives = noOfConsecutives + 1
    ConsecutiveVal = cll.Value
    If noOfConsecutives > 3 Then
        FoundNoOfConsecutives = noOfConsecutives
        FoundConsecutiveVal = ConsecutiveVal
    End If
Else
    noOfConsecutives = 1
    ConsecutiveVal = -1
End If
Next cll
If FoundNoOfConsecutives Then
    Range("H" & rw).ClearComments
    Range("H" & rw).AddComment
    Range("H" & rw).Comment.Text Text:="there were  " _
    & FoundNoOfConsecutives & " " & FoundConsecutiveVal & "s"
Else
    Range("H" & rw).ClearComments
End If


Next rw


End Sub

Display More

The above code has to be manually run but it could be made to run on a worksheet change event if the specified range is changed.

In column H I just put the formula
=sum(A1:G1) in the first row and copied down.

At the very least it should give you some suggestions.

p45cal

Quote from ceruin

Can anyone suggest a formula to automatically comment a cell when rows with given number consecutive columns has same entry example:

A B C D E F G H
1 0 0 0 0 5 6 7 18
2 0 0 5 6 7 0 8 26
3 0 5 0 0 0 0 0 5

row 1: there are minimum 4 '0' in consecutive columns, so H1 is sum of A1:G1 = 18 and automatically comment cell H1 "there are 4 zero"

row 2: because there are no 4 '0' in consecutive columns, H2 is 26 , and no comment

row 3: there are 5 '0' in consecutive columns, so H3 is 5 and automatically commented cell H3 "there are 5 zero"

Display More

ceruin · Jun 22nd 2007

Re: Automatically Comment Cell When Row With Consecutive Column Has Same Entry

Dear p45cal,

What do you mean by: "it could be made to run on a worksheet change event if the specified range is changed."?

Thanks for your help, now I have a better understanding of VBA on excel. You are my champion! :smile:

Dave Hawley · Jun 22nd 2007

Re: Automatically Comment Cell When Row With Consecutive Column Has Same Entry

Is it always Zeros?

ceruin · Jun 22nd 2007

Re: Comment Cell When Row With Consecutive Column Has Same Entry

Hello,

It's not always.

I am still trying to find out how to do this without running it manually, ie: via function.

Somehow at times I keep getting #NAME?

*sigh*

Dave Hawley · Jun 22nd 2007

Re: Comment Cell When Row With Consecutive Column Has Same Entry

#NAME! means the formula is not being recognized by Excel

Will Riley · Jun 22nd 2007

Re: Comment Cell When Row With Consecutive Column Has Same Entry

Quote from ceruin

I am still trying to find out how to do this without running it manually, ie: via function.

You will not get a function that adds a comment to a cell. a function returns a result of one or more evaluations in a cell - that's it.

You will need a code or conditional formatting based solution to your problem - a function will not do what you want.

p45cal · Jun 23rd 2007

Re: Automatically Comment Cell When Row With Consecutive Column Has Same Entry

This in the worksheet code module for the sheet in question (where the flashing cursor is when you right click on the worksheet's tab and choose 'View Code...'

Code

Private Sub Worksheet_Change(ByVal Target As Range)
Set isect = Intersect(Range("$A$1:$G$3"), Target)
If Not isect Is Nothing Then
Application.EnableEvents = False
        FoundNoOfConsecutives = 0
        FoundConsecutiveVal = -1
        noOfConsecutives = 1
        ConsecutiveVal = -1
        For Each cll In Range("B" & Target.Row & ":G" & Target.Row).Cells
            If cll.Value = cll.Offset(0, -1).Value Then
                noOfConsecutives = noOfConsecutives + 1
                ConsecutiveVal = cll.Value
                If noOfConsecutives > 3 Then
                    FoundNoOfConsecutives = noOfConsecutives
                    FoundConsecutiveVal = ConsecutiveVal
                End If
            Else
                noOfConsecutives = 1
                ConsecutiveVal = -1
            End If
        Next cll
        If FoundNoOfConsecutives Then
            Range("H" & Target.Row).ClearComments
            Range("H" & Target.Row).AddComment
            Range("H" & Target.Row).Comment.Text Text:="there were  " _
            & FoundNoOfConsecutives & " " & FoundConsecutiveVal & "s"
        Else
            Range("H" & Target.Row).ClearComments
        End If
End If
Application.EnableEvents = True

Display More

The
Range("$A$1:$G$3")
bit is the specified range mentioned below. No checks have been put in to check that only one row of cells has been changed; it's just for ideas.

As far as making a user defined function is concerned, the following basic UDF will give a result in a cell (not its comment):

Code

Function blah2(myRange As Range)
       Application.Volatile
        FoundNoOfConsecutives = 0
        FoundConsecutiveVal = -1
        noOfConsecutives = 1
        ConsecutiveVal = -1
        i = myRange.Cells.Count
        myCount = 0
        For Each cll In myRange.Cells
            myCount = myCount + 1
            If cll.Offset(0, 1).Value = cll.Value Then
                noOfConsecutives = noOfConsecutives + 1
                ConsecutiveVal = cll.Value
                If noOfConsecutives > 3 Then
                    FoundNoOfConsecutives = noOfConsecutives
                    FoundConsecutiveVal = ConsecutiveVal
                End If
            Else
                noOfConsecutives = 1
                ConsecutiveVal = -1
            End If
            If myCount + 1 >= i Then Exit For
        Next cll
        If FoundNoOfConsecutives Then
            blah2 = "there were  " _
            & FoundNoOfConsecutives & " " & FoundConsecutiveVal & "s"
        Else
            blah2 = "None"
        End If
End Function

Display More

Put this preferably in a standard code module.
It won't work for anything but a single contiguous row of cells with at least 2 cells, but you're looking for more than 3 consecutives anyway.

p45cal

Quote from ceruin

Dear p45cal,

What do you mean by: "it could be made to run on a worksheet change event if the specified range is changed."?

Thanks for your help, now I have a better understanding of VBA on excel. You are my champion! :smile:

Comment Cell When Row With Consecutive Column Has Same Entry

Participate now!

Share