How to convert a cell to a number format for vlookup

fvisions

Board Regular
Joined
Jul 29, 2008
Messages
182
Office Version
  1. 365
Platform
  1. Windows
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?

1604584536883.png

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
 

Some videos you may like

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
Joined
Aug 1, 2002
Messages
55,454
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Dec 19, 2016
Messages
23
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
Joined
Aug 1, 2002
Messages
55,454
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Jul 29, 2008
Messages
182
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Aug 1, 2002
Messages
55,454
Office Version
  1. 365
Platform
  1. Windows
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!
 

Watch MrExcel Video

Forum statistics

Threads
1,118,755
Messages
5,574,044
Members
412,565
Latest member
roberttaekim
Top