VBA Vlookup function returns wrong values

Hi michson! I have tried and run the code. It indeed is working with the importation of numbers. However, the value imported is still in text format even after the alternation of the code from this:

 With range("K4:K43")
        .Cells.Copy
        .Cells.PasteSpecial xlPasteValues
        .Cells(1).Select
    End With
    Application.CutCopyMode = False

to this:

With targetSheet.range("K4:K43")
        .Cells.Copy
        .Cells.PasteSpecial xlPasteValuesAndNumberFormats
        .Cells(1).Select
    End With
    Application.CutCopyMode = False

and so the column next to it cannot carry out its designated functions as it only recognizes numeric value. I have tried using "range value" to fix the problem but it is still showing up as formula only in the cell.

Also, I have tried using the following codes to remove the #N/A in the cells but none of them worked.

Cells.SpecialCells(xlCellTypeFormulas, xlErrors).Clear

Cells.Replace "#N/A", "clear", xlWhole

This one worked a few times and then suddenly stopped working:

Cells.SpecialCells(xlCellTypeFormulas, xlErrors).Clearcontent

Updates: I have fixed the numeric value problem by multiplying the formula with 1 so that the text format is forced into becoming numeric values in cells. Now only remains the #N/A problem.

Would you please give me some pointers to that? Thank you very much.