Question about this code::

[spcalan]

Sub Prepare()

Application.EnableEvents = False
Application.ScreenUpdating = False

With Sheets("Sheet1")
Sheets("sheet1").Select

FR = 2
LR = .Cells(Rows.Count, "A").End(xlUp).Row

'Create New ColB
.Columns("B:G").Insert shift:=xlToRight

'Create New Headers
.Range("B1:G1") = Array("Length", "Freezer", "Isle", "Bay", "Level", "Category")

'Convert Bay ID to number
Columns("F:F").Select
Selection.TextToColumns Destination:=Range("F1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
:=Array(1, 1), TrailingMinusNumbers:=True

'Enter Formulas
'Length Formula
.Range("B" & FR & ":B" & LR).Formula = "=LEN(A2)"

'Freezer Formula
.Range("C" & FR & ":C" & LR).Formula = "=LEFT(A2,2)"

'Isle Formula
.Range("D" & FR & ":D" & LR).Formula = "=MID(A2,3,1)"

'Bay Formula
.Range("E" & FR & ":E" & LR).Formula = "=MID(A2,4,2)"

'Level Formula
.Range("F" & FR & ":F" & LR).Formula = "=RIGHT(A2,1)"

'Category Formula
.Range("G" & FR & ":G" & LR).Formula = "=VLOOKUP(F2,'Warehouse Layout'!D:E,2,FALSE)"


'Copy / Paste Special All Data
.Cells.Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("A1").Select

'Delete all non 6 digit locations
For x = LR To 2 Step -1
If .Cells(x, "B") <> 6 Then
.Rows(x).EntireRow.Delete
End If
Next x
End With
End Sub

My issue is ColF is not a number, so I have use the "text to columns" to covert to a number, then run the vlookup for ColG.

But for some reason when I run the code all together, ColF is not coverted therefore my vlookup returns an error ( not a number ).

But I can run the "text to columns" number format stand alone, and it works.

What gives?

 

[Andrew Poulsom]

You seem to be missing a sheet qualifier:

.Columns("F:F").TextToColumns Destination:=.Range("F1"), DataType:=xlDelimited, _
    TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
    Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
    :=Array(1, 1), TrailingMinusNumbers:=True