How to convert a cell to a number format for vlookup

[fvisions]

I have a large file with a vba code that does several things. One being a vlookup from a column. The vlookup results are #N/A due to the lookup value needing to be converted to a number. How do I update the code to go to column i and convert the full column to a number?

 Dim LastRow As Long
    LastRow = Cells(Rows.Count, 2).End(xlUp).Row
    Range("K2").Select
    Application.CutCopyMode = False
    Application.CutCopyMode = False
    Range("K2:K" & LastRow).FormulaR1C1 = "=VLOOKUP(RC[-2],'Plant and Status List'!R3C5:R1071C8,4,FALSE)"
    Range("K2:K" & LastRow).Value = Range("K2:K" & LastRow).Value
    Range("K1").Select

 

[Joe4]

Try changing this line:

    Range("K2:K" & LastRow).FormulaR1C1 = "=VLOOKUP(RC[-2],'Plant and Status List'!R3C5:R1071C8,4,FALSE)"

to this:

    Range("K2:K" & LastRow).FormulaR1C1 = "=VLOOKUP(RC[-2]+0,'Plant and Status List'!R3C5:R1071C8,4,FALSE)"

 

[queuesmef]

Add 'Helper' column to Convert the data (use =value(cell) )and then change your Vlookup formula to look at the Valued cell.. Or change your lookup table to String values, if you have preceding zeros this may be your best way to handle this. if you posted a fuller example it would be easier to 'see' what you're doing.

 

[Joe4]

Add 'Helper' column to Convert the data (use =value(cell) )and then change your Vlookup formula to look at the Valued cell.. Or change your lookup table to String values, if you have preceding zeros this may be your best way to handle this. if you posted a fuller example it would be easier to 'see' what you're doing.

I don't think there should be any need to go to these lengths.
You should be able to easily adapt the VLOOKUP formula like I have shown to address the issue.
If you have a number entered as text that you need to treat like a number, all you need to do is to coerce it to a number by performing some simple mathematical computation on it, like adding zero or multiplying by 1. And you can do that right in the VLOOKUP formula, like I have shown above.

 

[fvisions]

I don't think there should be any need to go to these lengths.
You should be able to easily adapt the VLOOKUP formula like I have shown to address the issue.
If you have a number entered as text that you need to treat like a number, all you need to do is to coerce it to a number by performing some simple mathematical computation on it, like adding zero or multiplying by 1. And you can do that right in the VLOOKUP formula, like I have shown above.

IT IS RUNNING PERFECTLY NOW...YOU ARE THE BEST!!! I have a meeting today with my boss to show him and I was stressed to the max. Thank you, Thank you, Thank you

 

[Joe4]

IT IS RUNNING PERFECTLY NOW...YOU ARE THE BEST!!! I have a meeting today with my boss to show him and I was stressed to the max. Thank you, Thank you, Thank you

You are welcome.
Glad we were able to help you out!