VBA Code Alteration

[Justinian]

How do I alter the following code so that instead of autofill stopping at B68, the autofill stops at the last row of data in column A?

Sub VLOOKUP()

' Run VLOOKUP formula starting in B9
Range("B9").Select
Selection.FormulaR1C1 = _
"=IFERROR(IF(OR(LEFT(RC[-1],1)=""9"",LEFT(RC[-1],2)<=""32""),VLOOKUP(RC[-1],Gardens,2,FALSE),RC[-1]),"""")"
Selection.AutoFill Destination:=Range("B9:B68")
Range("B9:B68").Select
Range("A1").Select
End Sub

 

[JoeMo]

Maybe:

Sub VLOOKUP()
Dim LastRw As Long
LastRw = Cells(Rows.Count, "A").End(xlUp).Row
' Run VLOOKUP formula starting in B9
With Range("B9")
    .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("B9:B" & LastRw)
End With
Range("A1").Select
End Sub

 

[Justinian]

Ok, that works!

What if I wanted to run this code prior to running the VLOOKUP? Do I need to declare any other variables or can I just add it in?

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

 

[JoeMo]

Why not just give it a try to find the answer?

 

[Justinian]

I did but it did not work because some of the results in column A were not formatted as a number so I had to convert after running the macro. How would I add that to the code?

 

[JoeMo]

I did but it did not work because some of the results in column A were not formatted as a number so I had to convert after running the macro. How would I add that to the code?

Columns("A").NumberFormat = "your desired format between the quote marks"

 

[Justinian]

What I meant is the code only runs on cell values stored as numbers. How do I alter the code to convert all of column A to number from text?

 

[JoeMo]

What I meant is the code only runs on cell values stored as numbers. How do I alter the code to convert all of column A to number from text?

Try:

Range("A1:A" & Range("A" & Rows.Count).End(xlUp).Row).TextToColumns Destination:=Range("A1")

 

[Justinian]

Where in my code do I put that? I tried putting it below .Copy but nothing happened.

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

 

[JoeMo]

I wouldn't put it immediately after Copy. Try it after the paste. Alternatively, I see that you are using a text function (RIGHT) to generate the values you want to be numbers rather than text, so you can generate numbers directly by using:

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