vlookup result in formula not value

[Costa_Mukhar]

Hello experts, I am using the below code for a vlookup, it is working perfectly except that in the first cell it is giving the result in value not formula

Dim cellvalue As String
    Dim lookup_range As range
    Dim coupon As Single
    
    cellvalue = Worksheets("Cost report").range("B5")
    Set lookup_range = Worksheets("BA").range("D:H")
    
    coupon = Application.WorksheetFunction.VLookup(cellvalue, lookup_range, 5, False)
    range("C5:C" & range("B" & Rows.Count).End(xlUp).Row) = coupon

in cell C5, the result is in value, so when the macro applies the formula on the selected range, it is copying the same value in cell C5 to the entire range. how to keep the formula in the first cell, so it can apply it on the entire range then turn the entire column into values. I feel that there is something missing in the code, but I am not able to figure it out!!

Thanks in advnace

 

[Costa_Mukhar]

YES....I got it it feels amazing when you figure it by yourself

Dim cellvalue As String
    Dim lookup_range As range
    Dim coupon As Single
    
    cellvalue = Worksheets("Cost report").range("B5:B" & Rows.Count).End(xlUp).Row
    Set lookup_range = Worksheets("BA").range("D:H")
    
    'coupon = Application.WorksheetFunction.VLookup(cellvalue, lookup_range, 5, False)
    'range("C5:C" & range("B" & Rows.Count).End(xlUp).Row) = coupon
    
    With Worksheets("cost report").range("C5:C" & range("B" & Rows.Count).End(xlUp).Row)
        .FormulaR1C1 = "=vlookup(RC[-1],'BA'!C4:C8,5,False)"
        .FormulaR1C1 = .Value
        
    End With