Find 'Missing Coverage' in Column, Then insert a comment in the same row but different column.

[bmkelly]

Hello,

I am wanting to have the code look to find "Missing Coverage" in a Column that has a header of "Coverage", then when found I need to insert a comment in the same row but different column labeled "Analyst Notes".... Is there a way I can have this go through in a loop for each cell in the column "Coverage" to find "Missing Coverage" to then add a comment in a few columns over in the "Analyst Notes" cell?

Thanks

 

[Fluff]

A few questions.
What column is Coverage in?
What column is Analyst Notes in?
What is the comment you want to use?

 

[bmkelly]

A few questions.
What column is Coverage in?
What column is Analyst Notes in?
What is the comment you want to use?

Coverage is in Column N
Analyst Notes is in Column V
Comment I would like is "Review - Missing Coverage"

If possible, I would like the code to search in the headers for "Coverage" then go down the columns cells to search for Missing Coverage etc. That way if any other analyst decides to add any columns it could potentially mess up the code.

Thanks!

 

[Fluff]

Ok, how about

Sub bmkelly()
   Dim Ccol As Range, ANcol As Range
  
   Set Ccol = Range("1:1").Find("Coverage", , , xlWhole, , , False, , False)
   If Ccol Is Nothing Then
      MsgBox "Coverage column not found"
      Exit Sub
   End If
   Set ANcol = Range("1:1").Find("Analyst Notes", , , xlWhole, , , False, , False)
   If ANcol Is Nothing Then
      MsgBox "Analyst Notes column not found"
      Exit Sub
   End If
  
   With Ccol.EntireColumn
      .Replace "Missing Coverage", "=true", xlWhole, , False, , False, False
      On Error Resume Next
      .SpecialCells(xlFormulas, xlLogical).Offset(, ANcol.Column - .Column).Value = "Review - Missing Coverage"
      On Error GoTo 0
   End With
End Sub

This assumes you do not have a formula in the Coverage column that could return True

 

[bmkelly]

Ok, how about

Sub bmkelly()
   Dim Ccol As Range, ANcol As Range
 
   Set Ccol = Range("1:1").Find("Coverage", , , xlWhole, , , False, , False)
   If Ccol Is Nothing Then
      MsgBox "Coverage column not found"
      Exit Sub
   End If
   Set ANcol = Range("1:1").Find("Analyst Notes", , , xlWhole, , , False, , False)
   If ANcol Is Nothing Then
      MsgBox "Analyst Notes column not found"
      Exit Sub
   End If
 
   With Ccol.EntireColumn
      .Replace "Missing Coverage", "=true", xlWhole, , False, , False, False
      On Error Resume Next
      .SpecialCells(xlFormulas, xlLogical).Offset(, ANcol.Column - .Column).Value = "Review - Missing Coverage"
      On Error GoTo 0
   End With
End Sub

This assumes you do not have a formula in the Coverage column that could return True

That's perfect! One thing....and I tried to remove it but then it messes up the code is instead of replacing Missing Coverage with TRUE can it just stay as Missing Coverage?

 

[Fluff]

Oops, missed line it should be

Sub bmkelly()
   Dim Ccol As Range, ANcol As Range
  
   Set Ccol = Range("1:1").Find("Coverage", , , xlWhole, , , False, , False)
   If Ccol Is Nothing Then
      MsgBox "Coverage column not found"
      Exit Sub
   End If
   Set ANcol = Range("1:1").Find("Analyst Notes", , , xlWhole, , , False, , False)
   If ANcol Is Nothing Then
      MsgBox "Analyst Notes column not found"
      Exit Sub
   End If
  
   With Ccol.EntireColumn
      .Replace "Missing Coverage", "=true", xlWhole, , False, , False, False
      On Error Resume Next
      .SpecialCells(xlFormulas, xlLogical).Offset(, ANcol.Column - .Column).Value = "Review - Missing Coverage"
      On Error GoTo 0
      .Replace "=true", "Missing Coverage", xlWhole, , False, , False, False
   End With
End Sub

 

[bmkelly]

T

Oops, missed line it should be

Sub bmkelly()
   Dim Ccol As Range, ANcol As Range
 
   Set Ccol = Range("1:1").Find("Coverage", , , xlWhole, , , False, , False)
   If Ccol Is Nothing Then
      MsgBox "Coverage column not found"
      Exit Sub
   End If
   Set ANcol = Range("1:1").Find("Analyst Notes", , , xlWhole, , , False, , False)
   If ANcol Is Nothing Then
      MsgBox "Analyst Notes column not found"
      Exit Sub
   End If
 
   With Ccol.EntireColumn
      .Replace "Missing Coverage", "=true", xlWhole, , False, , False, False
      On Error Resume Next
      .SpecialCells(xlFormulas, xlLogical).Offset(, ANcol.Column - .Column).Value = "Review - Missing Coverage"
      On Error GoTo 0
      .Replace "=true", "Missing Coverage", xlWhole, , False, , False, False
   End With
End Sub

This is perfect! Thank you. I might be in touch down the road as I will most likely try to utilize this code to help me essentially do the same thing but with different columns etc. but same logic!

 

[Fluff]

Glad to help & thanks for the feedback.

 

[bmkelly]

Glad to help & thanks for the feedback.

Youre welcome! So I essentially took the same code but I am wanting it to look in the Transaction Type Column and if the cell says Addition I need it to look at the WarrantyEnd Column and if the cell is Blank I need it to make a note in the Analyst Notes Column and say "Review - Missing Warranty - Addition" I follow most of the code but the .SpecialCells part so I assume I need to have something in my code to searh for a blank cell in WarrantyEnd column...

Sub BlankWarranty()

'Looks for Additions with a Blank Warranty in WarrantyEnd Column'

   Dim TTol As Range
   Dim WECol As Range
   Dim ANCol As Range
 
    Set TTCol = Range("1:1").Find("Transaction Type", , , xlWhole, , , False, , False)
        If TTCol Is Nothing Then
            MsgBox "Transaction Type Column Not Found"
        Exit Sub
   
    End If
 
    Set WECol = Range("1:1").Find("WarrantyEnd", , , xlWhole, , , False, , False)
        If WECol Is Nothing Then
            MsgBox "WarrantyEnd Column Not Found"
        Exit Sub
     
    End If
 
    Set ANCol = Range("1:1").Find("Analyst Notes", , , xlWhole, , , False, , False)
        If ANCol Is Nothing Then
            MsgBox "Analyst Notes Column Not Found"
        Exit Sub
     
    End If
 
    With TTCol.EntireColumn
        On Error Resume Next
            .SpecialCells(xlFormulas, xlLogical).Offset(, ANCol.Column - .Column).Value = "Review - Blank Warranty - Addition"
        On Error GoTo 0
     
    End With
 
    With TTCol.EntireColumn
        .Replace "Addition", "=true", xlWhole, , False, , False, False
            On Error Resume Next
        .SpecialCells(xlFormulas, xlLogical).Offset(, ANCol.Column - .Column).Value = "Review - Blank Warranty - Addition"
            On Error GoTo 0
        .Replace "=true", "Addition", xlWhole, , False, , False, False
     
    End With
 
End Sub

 

[Fluff]

That is a significantly different question & will need a different approach. So I suggest that you start a new thread for this question.