VBA Code to Convert to Number

[Justinian]

How do I alter this code so the results of the VLOOKUP and RIGHT formulas are converted from text to number?

Sub VLOOKUP()
Dim LastRw As Long
LastRw = Cells(Rows.Count, "A").End(xlUp).Row

' Run VLOOKUP formula starting in B6
With Range("B6")
.FormulaR1C1 = _
"=IFERROR(IF(OR(LEFT(RC[-1],1)=""9"",LEFT(RC[-1],2)<=""32""),VLOOKUP(RC[-1],Gardens,2,FALSE),RC[-1]),"""")"
.AutoFill Destination:=Range("B6:B" & LastRw)
End With

' Applies RIGHT formula to column I and pastes resulting values into column A
With Range("I6:I" & Range("A" & Rows.Count).End(xlUp).Row)
.FormulaR1C1 = "=RIGHT(RC[-8],LEN(RC[-8])-FIND(""-"",RC[-8],1))"
.Copy
Range("A6").PasteSpecial xlPasteValues
.ClearContents
End With

ActiveSheet.PageSetup.PrintArea = Range("A4").CurrentRegion.Address

End Sub

 

[Justinian]

How do I do that? When I ran the RIGHT formula, everything is formatted as general.

 

[Fluff]

How do I do that?

I have absolutely no idea as I cannot see your data, have no idea where it came form, what (if anything) you have done to it etc.

 

[Justinian]

Ok, here is what the data looks like prior to running any code:

Move-Ins.xlsm
	A	B	C
5	Building/Unit	Street Address	Scheduled Move-In Date
6	10-1015		07/21/20
7	10-1023		07/21/20
8	19-1955		07/21/20
9	23-2342		07/21/20
10	24-2411		07/21/20
11	28-2856		07/21/20
12	33-3309J		07/21/20
13	34-3400H		07/21/20
14	36-3608L		07/21/20
Report

Here is what the data looks like after running the RIGHT formula:

Move-Ins.xlsm
	A	B	C
5	Building/Unit	Street Address	Scheduled Move-In Date
6	1015		07/21/20
7	1023		07/21/20
8	1955		07/21/20
9	2342		07/21/20
10	2411		07/21/20
11	2856		07/21/20
12	3309J		07/21/20
13	3400H		07/21/20
14	3608L		07/21/20
Report

Finally, here is what the data looks like after running VLOOKUP:

Move-Ins.xlsm
	A	B	C
5	Building/Unit	Street Address	Scheduled Move-In Date
6	1015		07/21/20
7	1023		07/21/20
8	1955		07/21/20
9	2342		07/21/20
10	2411		07/21/20
11	2856		07/21/20
12	3309J	3309J	07/21/20
13	3400H	3400H	07/21/20
14	3608L	3608L	07/21/20
Report

Cell Formulas
Range	Formula
B6:B14	B6	=IFERROR(IF(OR(LEFT(A6,1)="9",LEFT(A6,2)<="32"),VLOOKUP(A6,Gardens,2,FALSE),A6),"")

Named Ranges
Name	Refers To	Cells
Gardens	='Garden Addresses'!$A$1:$B$1622	B6:B14

The function works but because rows 6-11 are formatted as text, I have to manually convert to numbers in order to gain the final result:

Move-Ins.xlsm
	A	B	C
5	Building/Unit	Street Address	Scheduled Move-In Date
6	1015	6120 W. 1st Street # 10-15	07/21/20
7	1023	6104 W. 2nd Street # 10-23	07/21/20
8	1955	593 1/2 S. 10th Street # 19-55	07/21/20
9	2342	6117 W. 2nd Street # 23-42	07/21/20
10	2411	6061 W. Fifth Street # 24-11	07/21/20
11	2856	5906 W. 1st Street # 28-56	07/21/20
12	3309J	3309J	07/21/20
13	3400H	3400H	07/21/20
14	3608L	3608L	07/21/20
Report

Cell Formulas
Range	Formula
B12:B14	B12	=IFERROR(IF(OR(LEFT(A12,1)="9",LEFT(A12,2)<="32"),VLOOKUP(A12,Gardens,2,FALSE),A12),"")

Named Ranges
Name	Refers To	Cells
Gardens	='Garden Addresses'!$A$1:$B$1622	B12:B14

 

[Fluff]

Ok, try using this formula

=iferror(RIGHT(RC[-8],LEN(RC[-8])-FIND(""-"",RC[-8],1))+0,=RIGHT(RC[-8],LEN(RC[-8])-FIND(""-"",RC[-8],1)))

 

[Fluff]

In what way is this thread different from this one VBA Code Alteration ?

 

[Justinian]

I never got a reply back so I thought a new thread might be less confusing than a bump.

I tried your code but the debugger gets hung up on that new code you gave me.

 

[Fluff]

I never got a reply back so I thought a new thread might be less confusing than a bump.

In that case I will close this thread. Please do not make duplicate threads in future.