Using VBA to reference a changable Named Range

[Gingertrees]

To pull notes from an old worksheet into the latest worksheet in a workbook, I'm trying to automate the process of adding in a VLOOKUP into a sheet. (Each day I note things in a worksheet and save the notes as a named range in the workbook.) The challenge is that the named range this VLOOKUP references changes day to day. Here's what I've got so far:

Sub Test2()


    Dim Nrng As String
    Dim LstRow As Integer
    LstRow = ActiveSheet.UsedRange.Rows(ActiveSheet.UsedRange.Rows.Count).Row
    Nrng = InputBox("Enter Named Range for old notes")


    Dim RowrngIII As Range
    Dim zcell As Range
    
    Set RowrngIII = Range("AA3:AA" & LstRow)
    For Each zcell In RowrngIII
        zcell.Formula = "=VLOOKUP(RC[-20],Nrng,23,FALSE)"
    Next zcell


    Range("Q2").Select


End Sub

This puts the correct formula in my AA column, EXCEPT for the fact that it lists the source table/range as "Nrng" instead of "Maysix" or "MayXX" etc.

Is there a better way?

 

[Mike LH]

Hi,

Try it like this

Sub Test2()


    Dim Nrng As String
    Dim LstRow As Integer
    LstRow = ActiveSheet.UsedRange.Rows(ActiveSheet.UsedRange.Rows.Count).Row
    Nrng = InputBox("Enter Named Range for old notes")


    Dim RowrngIII As Range
    Dim zcell As Range
    
    Set RowrngIII = Range("AA3:AA" & LstRow)
    For Each zcell In RowrngIII
        zcell.Formula = "=VLOOKUP(RC[-20]," & Nrng & " ,23,FALSE)"
    Next zcell


    Range("Q2").Select


End Sub

 

[Gingertrees]

Thanks! Worked like a charm