none
Run code on Col names instead of Col header RRS feed

  • Question

  • I have the below code that run by using the Col header, like Col A, Col B etc..Instead I want to replace this with the col names used for Col A, Col B.

    I saw in other posts here that we can use lookAt:=xlWhole and .EntireColumn to achieve this. But I'm not quite sure on how to use this in my code below:

    Sub Test()
    
    Dim Rng As Range, cl As Range
    Dim LastRow As Long, MatchRow As Variant
    
     With Sheets("DRG")
        LastRow = .Cells(.Rows.count, "E").End(xlUp).Row '<-- find last row with data in column E
        Set Rng = .Range("E2:E" & LastRow)
    End With
    
    With Sheets("Latency")
        For Each cl In .Range("B2:B" & .Cells(.Rows.count, "B").End(xlUp).Row) ' loop through all cells in Column B
            MatchRow = Application.Match(cl.Value, Rng, 0) 
            If Not IsError(MatchRow) Then 
    
            Select Case Sheets("DRG").Range("AH" & MatchRow + 1).Value 
                Case "Approved"
                    .Range("O" & cl.Row).Value = "Pass"
    
                Case "Pended"
                    .Range("O" & cl.Row).Value = "Fail"
    
                Case "In progress"
                    .Range("O" & cl.Row).Value = "In progress"
            End Select
    
                  If Not Sheets("DRG").Range("E" & MatchRow + 1).Value = vbNullString Then .Range("P" & cl.Row).Value = .Range("P" & cl.Row).Value & IIf(Not .Range("P" & cl.Row).Value = vbNullString, ";", "") & Sheets("DRG").Range("S" & MatchRow + 1).Value
        End If
    Next cl
    End With
    
    End Sub

    Saturday, February 11, 2017 5:43 AM

All replies

  • I have the below code that run by using the Col header, like Col A, Col B etc..Instead I want to replace this with the col names used for Col A, Col B.

    I guess you mixed up "header" and "names", in other words:

    The code below compares each row of the columns E and B in two sheets. I want to search for the headings in that sheets instead of using the hard coded column names E and B.

    Is this what you want to achieve?

    Andreas.

    Sunday, February 12, 2017 8:02 AM