Vlookup loop with dynamic, named ranges

Question

I have set three columns as named, dynamic ranges in my workbook. I can't figure out how to loop the vlookup function with all three named ranges. My named ranges are defined and working, I just can't get them working in the vlookup loop.

Once the vlookup finishes, I do another loop to replace any results with the value "alerted". I used to have this vlookup working with non-named ranges, but my data is changing so much I'd rather do this fully with named ranges.

Note: ALERTED_TRANSACTION_IND starts as a blank column but has a header

Sub LOOKUP_TEST()



 'declaring variables
    Dim w As Worksheet
    Dim w2 As Worksheet
    Dim wLastrow As Long
    Dim w2Lastrow As Long
    Dim x As Long
    
    'Transaction ID named range
    Dim rngHeaders_alerted As Range
    Dim rngHdrFound_alerted As Range
    Dim Transaction_ID As Range
    
    'Transaction_Number named range
    Dim rngHeaders_raw As Range
    Dim rngHdrFound_raw As Range
    Dim Transaction_Number As Range
    
    'ALERTED_TRANSACTION_IND named range
    Dim rngHeaders_raw1 As Range
    Dim rngHdrFound_raw1 As Range
    Dim ALERTED_TRANSACTION_IND As Range
    
    
    
    'setting the variables to match the sheet names from the initial formatting macro
    Set w = ActiveWorkbook.Worksheets("Raw")
    Set w2 = ActiveWorkbook.Worksheets("ALERTED_TRANSACTIONS")
    
    
    
    'Setting named range for Transaction ID in the Alerted Txn Output
    Const ROW_HEADERS_ALERTED As Integer = 1
    Const HEADER_NAME_ALERTED As String = "Transaction ID"
    Set rngHeaders_alerted = Intersect(w2.UsedRange, w2.Rows(ROW_HEADERS_ALERTED))
    Set rngHdrFound_alerted = rngHeaders_alerted.Find(HEADER_NAME_ALERTED)
    Set Transaction_ID = Range(rngHdrFound_alerted, rngHdrFound_alerted.End(xlDown))
    '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
    
    
    
    'Setting named range for Transaction Number in the Raw Txn Output
    Const ROW_HEADERS_RAW As Integer = 1
    Const HEADER_NAME_RAW As String = "Transaction_Number"
    Set rngHeaders_raw = Intersect(w.UsedRange, w.Rows(ROW_HEADERS_RAW))
    Set rngHdrFound_raw = rngHeaders_raw.Find(HEADER_NAME_RAW)
    Set Transaction_Number = Range(rngHdrFound_raw, rngHdrFound_raw.End(xlDown))
    '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
    
    
    
    'Setting named range for Transaction Number in the Raw Txn Output
    Const ROW_HEADERS_RAW1 As Integer = 1
    Const HEADER_NAME_RAW1 As String = "ALERTED_TRANSACTION_IND"
    Set rngHeaders_raw1 = Intersect(w.UsedRange, w.Rows(ROW_HEADERS_RAW1))
    Set rngHdrFound_raw1 = rngHeaders_raw1.Find(HEADER_NAME_RAW1)
    Set ALERTED_TRANSACTION_IND = Range(rngHdrFound_raw1, rngHdrFound_raw1.End(xlDown))
    '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
    
    
    'finding last row of transaction_number range
    wLastrow = Transaction_Number.End(xlUp).Row
    
    
    'using a For statement to execute the vlookup in the entire column
    For x = 2 To wLastrow
     ' On Error Resume Next
       ALERTED_TRANSACTION_IND.Value = Application.WorksheetFunction.VLookup(Transaction_Number.Value, Transaction_ID, 1, False)
    Next x
    
    
    
    'This statement replaces the ALERTED_TRANSACTION_IND id with ALERTED_TRANSACTION_IND, this is necessary for the filtering in the pivot tables
    For Each C In ALERTED_TRANSACTION_IND
        If Not IsEmpty(C.Value) Then C.Value = "Alerted"
    Next