entering a vlookup formula into a cell using vba

[Brenton Hombsch]

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

 

[CalcSux78]

Try:

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

 

[Brenton Hombsch]

Thankyou!