# How to convert a cell to a number format for vlookup

#### fvisions

##### Board Regular
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?

VBA Code:
`````` 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``````

### Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.

#### Joe4

##### MrExcel MVP, Junior Admin
Try changing this line:
Rich (BB code):
``    Range("K2:K" & LastRow).FormulaR1C1 = "=VLOOKUP(RC[-2],'Plant and Status List'!R3C5:R1071C8,4,FALSE)"``
to this:
Rich (BB code):
``    Range("K2:K" & LastRow).FormulaR1C1 = "=VLOOKUP(RC[-2]+0,'Plant and Status List'!R3C5:R1071C8,4,FALSE)"``

#### queuesmef

##### New Member
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

##### MrExcel MVP, Junior Admin
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

##### Board Regular
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

##### MrExcel MVP, Junior Admin
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.

Replies
10
Views
133
Replies
3
Views
124
Replies
7
Views
88
Replies
5
Views
165
Replies
7
Views
207