VBA code to read headers

[bmkelly]

Hello I currently am trying to have my code Read/Find by Column Headers instead of Columns M, O, and U that you see in my code below. I just want to make sure that it reads and finds the headera and then goes through the code that way if anyone adds any columns it doesn't effect the code any.

Sub BlankWarranty()

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

    Dim r1 As String, r2 As String
    Dim lr As Long
  
        lr = Range("M" & Rows.Count).End(3).Row 'Transaction Type'
        r1 = Range("M2:M" & lr).Address 'Transaction Type'
        r2 = Range("O2:O" & lr).Address 'WarrantyEnd'
  
  'Notes added in Analyst Notes'
    Range("U2:U" & lr).Value = Evaluate("=IF(" & r1 & "=""Addition"",IF(ISBLANK(" & r2 & "),""Review - Blank Warranty Addition"",""""),"""")")

End Sub

 

[bmkelly]

When it's determined that the note needs to be put into the 'Site Manager Notes Column'
this line checks the number of characters that may already be in the cell using the Len function

If Len(.ListColumns("Site Manager Notes").DataBodyRange(i, 1).Value) = 0 Then

If there aren't any characters then the note is just written to the cell using

.ListColumns("Site Manager Notes").DataBodyRange(i, 1).Value = note

if there are characters in the cell the above line is passed over and the Else portion of the IF kicks in
the InStr function checks for the note already being in the cell

If InStr(.ListColumns("Site Manager Notes").DataBodyRange(i, 1).Value, note) = 0 Then

if note isn't already in the cell, it is added to what is alreay in the cell with this line

                   .ListColumns("Site Manager Notes").DataBodyRange(i, 1).Value = _
                    .ListColumns("Site Manager Notes").DataBodyRange(i, 1).Value & Chr(10) & note

Chr(10) is the ascii code for Alt+Enter which is what you have to use to move to a new line when typing directly in a cell.

Hope that helps
NoSparks

Thank you! You have helped me out tremendously