VLOOKUP Question (VBA)

Hi all,
I'm scratching my head on the below codes.
It was working in one of my spreadsheet but when I try to apply it to the new one I am working on, it does not seem to work.
Any idea what I am doing wrong?

Sub LookUp()

    Range("C6").Formula = "=IFERROR(VLOOKUP(RC[-1],'Sheet2'!C[-2]:C[-1],2,FALSE),"" "")"
    With Range("C6", "C" & Cells(Rows.Count, 1).End(xlUp).Row)
    .FillDown
    .Value = .Value
    End With
    
End Sub

I wanted to see value on the target cells not formulas.
Any suggestions? Or perhaps better way of doing this?

Thanks in advance.

Try this...

Sub MyLookup()
    With Range("C6", "C" & Cells(Rows.Count, 2).End(xlUp).Row)
        .Formula = "=IFERROR(VLOOKUP(RC[-1],'Sheet2'!C[-2]:C[-1],2,FALSE),"" "")"
        .Value = .Value
    End With
End Sub