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

 

[Snakehips]

@bmkelly
Not tested but try something like below. Edit for the appropriate headers.

Sub BlankWarranty()

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

    Dim r1 As String, r2 As String
    Dim lr As Long
    Dim eM As Long
    Dim Ow As Long
    Dim yU As Long
    
    eM = WorksheetFunction.Match("Header 1", Range("A1:AZ1"), 0)  ' Edit Headers!!
    Ow = WorksheetFunction.Match("Header 2", Range("A1:AZ1"), 0)
    yU = WorksheetFunction.Match("Header 3", Range("A1:AZ1"), 0)
    
    
        lr = Cells(Rows.Count, eM).End(3).Row  'Transaction Type'
        r1 = Range(Cells(2, eM), Cells(lr, eM)).Address 'Transaction Type'
        r2 = Range(Cells(2, Ow), Cells(lr, Ow)).Address 'WarrantyEnd'
  
  'Notes added in Analyst Notes'
    Range(Cells(yU, 2), Cells(yU, lr)).Value = Evaluate("=IF(" & r1 & "=""Addition"",IF(ISBLANK(" & r2 & "),""Review - Blank Warranty Addition"",""""),"""")")

End Sub

Hope that helps.

 

[bmkelly]

@bmkelly
Not tested but try something like below. Edit for the appropriate headers.

Sub BlankWarranty()

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

    Dim r1 As String, r2 As String
    Dim lr As Long
    Dim eM As Long
    Dim Ow As Long
    Dim yU As Long
  
    eM = WorksheetFunction.Match("Header 1", Range("A1:AZ1"), 0)  ' Edit Headers!!
    Ow = WorksheetFunction.Match("Header 2", Range("A1:AZ1"), 0)
    yU = WorksheetFunction.Match("Header 3", Range("A1:AZ1"), 0)
  
  
        lr = Cells(Rows.Count, eM).End(3).Row  'Transaction Type'
        r1 = Range(Cells(2, eM), Cells(lr, eM)).Address 'Transaction Type'
        r2 = Range(Cells(2, Ow), Cells(lr, Ow)).Address 'WarrantyEnd'
 
  'Notes added in Analyst Notes'
    Range(Cells(yU, 2), Cells(yU, lr)).Value = Evaluate("=IF(" & r1 & "=""Addition"",IF(ISBLANK(" & r2 & "),""Review - Blank Warranty Addition"",""""),"""")")

End Sub

Hope that helps.

I attempted this

@bmkelly
Not tested but try something like below. Edit for the appropriate headers.

Sub BlankWarranty()

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

    Dim r1 As String, r2 As String
    Dim lr As Long
    Dim eM As Long
    Dim Ow As Long
    Dim yU As Long
   
    eM = WorksheetFunction.Match("Header 1", Range("A1:AZ1"), 0)  ' Edit Headers!!
    Ow = WorksheetFunction.Match("Header 2", Range("A1:AZ1"), 0)
    yU = WorksheetFunction.Match("Header 3", Range("A1:AZ1"), 0)
   
   
        lr = Cells(Rows.Count, eM).End(3).Row  'Transaction Type'
        r1 = Range(Cells(2, eM), Cells(lr, eM)).Address 'Transaction Type'
        r2 = Range(Cells(2, Ow), Cells(lr, Ow)).Address 'WarrantyEnd'
 
  'Notes added in Analyst Notes'
    Range(Cells(yU, 2), Cells(yU, lr)).Value = Evaluate("=IF(" & r1 & "=""Addition"",IF(ISBLANK(" & r2 & "),""Review - Blank Warranty Addition"",""""),"""")")

End Sub

Hope that helps.

Thanks, unfortunately this code did not work for me when updating the headers.

 

[bmkelly]

bump

 

[bmkelly]

bump

Wondering if there is a way to edit/update this code to Read Column Headers and not the Column Letter. I would like for the code to look at Column Headers (Row 1) and if Column Header is "Transaction Type" (Column M) I would like for it to look for "Additions" then look for Column Header "WarrantyEnd" (Column O) and it is Blank then insert a note or add on note in Column Header "Site Manager Notes" (Column U) "Review - Blank Warranty Addition"

Sub BlankWarranty()

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

    Dim r1 As String
    Dim 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 Site Manager Notes'
    Range("U2:U" & lr).Value = Evaluate("=IF(" & r1 & "=""Addition"",IF(ISBLANK(" & r2 & "),""Review - Blank Warranty Addition"",""""),"""")")

End Sub

 

[NoSparks]

Deleted, apparently not needed any more, or is it?

 

[bmkelly]

Deleted, apparently not needed any more, or is it?

I am still needing help!

 

[NoSparks]

being no good with formulas I'd use a loop,

Sub Testing()
    Dim Trans As Range     'Transaction Type -> M equiv
    Dim WarEnd As Range    'WarrantyEnd -> O equiv
    Dim SiteMan As Range   'Site Manager Notes -> U equiv
    Dim rng As Range, cel As Range
    Dim lr As Long, note As String
    
note = "Review - Blank Warranty Addition"
Application.ScreenUpdating = False
With Sheets("Sheet1")
' TheSheet.Range.Find(What, After, LookIn, LookAt, SearchOrder, SearchDirection, MatchCase, MatchByte, SearchFormat)
    Set Trans = .Range("1:1").Find("Transaction Type", , xlValues, xlWhole)
    Set WarEnd = .Range("1:1").Find("WarrantyEnd", , xlValues, xlWhole)
    Set SiteMan = .Range("1:1").Find("Site Manager Notes", , xlValues, xlWhole)

    lr = .Cells(.Rows.Count, Trans.Column).End(xlUp).Row
    
    Set rng = .Range(.Cells(2, Trans.Column), .Cells(lr, Trans.Column))
    For Each cel In rng
        If cel.Value = Chr(34) & "Addition" & Chr(34) Then
            If .Cells(cel.Row, WarEnd.Column) = "" Then
                .Cells(cel.Row, SiteMan.Column).Value = Chr(34) & note & Chr(34)
            End If
        End If
    Next cel
End With
Application.ScreenUpdating = True
End Sub

if you don't like the loop, and have to use a formula, you can at least see how to get the columns
although your previous posts and their responses have used the range.find method enough for you to know this by now.

 

[BSALV]

i don't know how many columns,you're looking for, when you found one, you want to do something in another depending on the value in the found column.
The result of each line is a number, the column number,if the header is found, not a letter !
you can use it like cells(myrow,em).value="my value"

eM = WorksheetFunction.Match("Transaction Type", Range("A1:AZ1"), 0)  ' Edit Headers!!
    Ow = WorksheetFunction.Match("WarrantyEnd", Range("A1:AZ1"), 0)
    yU = WorksheetFunction.Match("Site Manager Notes", Range("A1:AZ1"), 0)

 

[bmkelly]

being no good with formulas I'd use a loop,

Sub Testing()
    Dim Trans As Range     'Transaction Type -> M equiv
    Dim WarEnd As Range    'WarrantyEnd -> O equiv
    Dim SiteMan As Range   'Site Manager Notes -> U equiv
    Dim rng As Range, cel As Range
    Dim lr As Long, note As String
   
note = "Review - Blank Warranty Addition"
Application.ScreenUpdating = False
With Sheets("Sheet1")
' TheSheet.Range.Find(What, After, LookIn, LookAt, SearchOrder, SearchDirection, MatchCase, MatchByte, SearchFormat)
    Set Trans = .Range("1:1").Find("Transaction Type", , xlValues, xlWhole)
    Set WarEnd = .Range("1:1").Find("WarrantyEnd", , xlValues, xlWhole)
    Set SiteMan = .Range("1:1").Find("Site Manager Notes", , xlValues, xlWhole)

    lr = .Cells(.Rows.Count, Trans.Column).End(xlUp).Row
   
    Set rng = .Range(.Cells(2, Trans.Column), .Cells(lr, Trans.Column))
    For Each cel In rng
        If cel.Value = Chr(34) & "Addition" & Chr(34) Then
            If .Cells(cel.Row, WarEnd.Column) = "" Then
                .Cells(cel.Row, SiteMan.Column).Value = Chr(34) & note & Chr(34)
            End If
        End If
    Next cel
End With
Application.ScreenUpdating = True
End Sub

if you don't like the loop, and have to use a formula, you can at least see how to get the columns
although your previous posts and their responses have used the range.find method enough for you to know this by now.

Thanks, I am still new to VBA and all self-taught so trying to still figure everything out. I ran it and had no errors but also no results so I will look into it a bit more.