how to add a comment on the first cell where a specific value is found and loop it

[klaael]

Hello,

I am trying to write a macro and I am having a hard time figuring how to go about it.

The end goal of the macro is to search if there is a value in column D starting at row 20, If TRUE then search for the first "TT" of the row and add a comment (value found in column D).
Then do it for the first TF and FT of the row that same row.
Then go on the next row until it reach row 499.

I am able to find the address of the cell with the specified value.

I am able to create a macro that writes the comment.

what I don't know how to mix them both together properly and apply the loop correctly to go through all the column.

Here a data Sample:

The cells in teal should get a comment. That comment is the value found in the cell on the same row in column D (under the "Dates" cell)

And here is the code I got so far, it gives me an error:

    Sub Add_Comments()

    Dim rng As Range
    Dim taskname As Long

    'Turns off Conditional formating
    Range("D1").Value = "NOCF"
    Application.Wait Now + #12:00:02 AM#


    For taskname = 20 To 499
    'Checks if there is a task on that row
    If Not IsEmpty(cell.Offset(taskname, 4).Value) Then
    'Start the loop to find and comment cells if value = TT, TF or FT
        Set rng = cell.Offset(taskname, 4)
        Set taskcmt = Selection.Find(What:="TT", LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)
            If Not taskcmt Is Nothing Then
    'Add a comment on the current address
            Cells(taskname, taskcmt).AddComment rng.Value
        
            End If
    'Turns on conditional formating
    Else: Range("D1").Value = "CF"
    Exit Sub
    End If
        
    Next
    'Turns on conditional formating
    Range("D1").Value = "CF"
    End Sub

 

[iliace]

You should specify where the error takes place. My guess is it is on this line:

 Cells(taskname, taskcmt).AddComment rng.Value

This is because Range.Find method returns a cell reference, and simply referring to it as "taskcmt" defaults to that cell's value, which I am guessing is not the column number (or perhaps not a number altogether).

Try replacing it with something like:

Cells(taskname, taskcmt.Column).AddComment rng.Value

<strike></strike>
​

 

[klaael]

Thanks, you 've put me on the right track and figured most of it out.

here is what I have now and I dont know how to make it continue if it doesnt find "TT" and how I could make it find "TT" on a row and add the comment then see of there is a "TF" and add the same comment and then do the same with "FT" before going to the next row.

Thanks, you are putting me on the right track and I fell I'm almost there.

Sub Add_Comments()

Dim taskname As String
Dim taskrng As Long


'Turns off Conditional formating
Range("D1").Value = "NOCF"
Application.Wait Now + #12:00:02 AM#


Cells(20, 28).Resize(499, 1104).Select
Selection.ClearComments


For taskrng = 20 To 499


'Checks if there is a task on that row
    Cells(taskrng, 4).Select
    If Cells(taskrng, 4).Value <> "" Then
'Keeping task name in memory
    taskname = Cells(taskrng, 4)
'Start the loop to find and comment cells if value = TT, TF or FT
        Cells(taskrng, 28).Resize(, 1104).Select
        Selection.Find(What:="TT", After:=ActiveCell, LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False).Select
        On Error Resume Next
        'Add a comment on the current selection
        Selection.AddComment taskname


    Else:
    End If


Next




'Turns on conditional formating
Range("D1").Value = "CF"


Cells(1, 1).Select
End Sub

I also didnt know anything about cross post so here is the link to the other post. I will post the final answer to all of them.

My goal was to test which site works best for me, not to break forum rules.
I'll add the links in my original post

 

[Fluff]

@klaael
You have mentioned Cross posting, but you have not supplied a link to your other posts.
Could you please do so?
Thanks

 

[klaael]

Sorry, I'm at work and must have been distracted while I was updating this post

Here they are:

https://www.ozgrid.com/forum/forum/...l-where-a-specific-value-is-found-and-loop-it

and

https://stackoverflow.com/questions...cell-where-a-specific-value-is-found-and-loop

 

[klaael]

This is how I ended doing it. One macro for each posibility ("TT", ""TF", "FT")
I am pretty sure this could be cleaned up and I could probably change the "FIND" to look for TT, then TF and finally FT but it was easier to duplicate the macro, erase what should only be done at the beginning or at the end and call the next macro so they could run one after the other.

Sub Add_Comments_TF()

Dim taskname As String
Dim taskrng As Long

Turns off Conditional formating
Range("D1").Value = "NOCF"
Application.Wait Now + #12:00:02 AM#

Cells(20, 27).Resize(499, 1104).Select
Selection.ClearComments

For taskrng = 20 To 499

'Checks if there is a task on that row
    Cells(taskrng, 4).Select
    If Cells(taskrng, 4).Value <> "" Then
'Keeping task name in memory
    taskname = Cells(taskrng, 4)
'Start the loop to find and comment cells if value = TT, TF or FT
        Cells(taskrng, 27).Resize(, 1104).Select
        Selection.Find(What:="TF", After:=ActiveCell, LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False).Select
        On Error Resume Next
        'Add a comment on the current selection
        Selection.AddComment taskname

    Else:
    End If

Next


Turns on conditional formating
Range("D1").Value = "CF"

Cells(1, 1).Select

Call Add_Comments_TT

End Sub