VBA Code to Match US Region to the State that is entered

[Paul_Hossler]

1. I added new sub LUV to return the n-th row bu searching the col headers row to find the column string each time


I added error message to help figure out what was wrong with the column header

I would have been a LOT easier if the column headers in MDS and MOST were called the same

Option Explicit

'Look Up value
Function LUV(rColHeaders As Range, sColHeader As String, N As Long) As Range
    Dim i As Long
    
    i = 0
    
    On Error Resume Next
    i = Application.WorksheetFunction.Match(sColHeader, rColHeaders, 0)
    On Error GoTo 0
        
    If i <> 0 Then
        Set LUV = rColHeaders.Parent.Cells(N, i)
    Else
        MsgBox "Column " & sColHeader & " not found in row " & rColHeaders.Address & " on worksheet " & rColHeaders.Parent.Name
    End If
End Function
Function GetColumnNumber(sColHeader As String, rColHeaders As Range) As Long
    Dim i As Long
    i = 0
    
    On Error Resume Next
    i = Application.WorksheetFunction.Match(sColHeader, rColHeaders, 0)
    On Error GoTo 0
        
    If i <> 0 Then
        GetColumnNumber = i
    Else
        MsgBox "Column " & sColHeader & " not found in row " & rColHeaders.Address & " on worksheet " & rColHeaders.Parent.Name
    End If
End Function

Additional Question: Why do I get the following when I go to look at my macro's

Probably because you had 2 workbooks open with the same sheet names and macro names