Results 1 to 3 of 3

Thread: entering a vlookup formula into a cell using vba

  1. #1
    New Member
    Join Date
    May 2019
    Posts
    6
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Post entering a vlookup formula into a cell using vba

    i am having trouble with the below code.

    i am trying to compare a cells text "Week1_Program" is the name of the cell
    with a list of that i have named each cell "WeekType1","WeekType2" etc
    and if they are the same return a range name eg"WeekType1Range","WeekType2Range"etc

    i then want to use this result to put into a vlookup formula to enter onto a worksheet

    Hope this makes some sense

    sub LookupRangeName()

    Dim R As String

    If Worksheets("Programs").Range("Week1_Program") = Worksheets("Programs").Range("WeekType1") Then
    R = "WeekType1Range"

    ElseIf Worksheets("Programs").Range("Week1_Program") = Worksheets("Programs").Range("WeekType2") Then
    R = "WeekType2Range"

    ElseIf Worksheets("Programs").Range("Week1_Program") = Worksheets("Programs").Range("WeekType3") Then
    R = "WeekType3Range"


    Endif

    Range("C4").Formula = "=VLOOKUP($B4,R,2,FALSE)"


    end sub

    Thanks in advance


  2. #2
    Board Regular CalcSux78's Avatar
    Join Date
    Oct 2013
    Location
    STL
    Posts
    1,120
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

    Default Re: entering a vlookup formula into a cell using vba

    Try:
    Code:
    Range("C4").Formula = "=VLOOKUP($B4," & R & ",2,FALSE)"
    Cunningham's Law: "The best way to get the right answer on the Internet is not to ask a question, it's to post the wrong answer."

  3. #3
    New Member
    Join Date
    May 2019
    Posts
    6
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: entering a vlookup formula into a cell using vba

    Thankyou!

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •