Avoid selection and select in format number

[sabael]

Hello.
I have a code to convert number stored as text to numbers.

I've read on the web much about to avoid using:
.Select
.Selection
ActiveSheet, etc.

However, I am unsure of how to avoid using those in my code:

Sub textTonumber()
Dim ws As Worksheet


Set ws = Worksheets("qry_creategantt")
        
    With ws.Range("I2", Range("I2").End(xlDown)).Select
     
        Selection.TextToColumns Destination:=Range("I2"), DataType:=xlDelimited, _
        TextQualifier:=xlTextQualifierDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Other:=False, FieldInfo:=Array(1, 1), TrailingMinusNumbers:=True
        ws.Range("A1").Select


    End With


End Sub

Your ideas are welcome
Cheer

[snb]

This is all you need:

Sub M_snb()
   sheets("qry_creategantt").colums(9).TextToColumns ,,,, -1,0, 0, 0 
End Sub

[sabael]

snb. Thanks for your reply.

I tested your code as follow:

Sub M_snb()   sheets("qry_creategantt").colums(9).TextToColumns , , , , -1, 0, 0, 0
End Sub

Sub M_snb()   Sheets("qry_creategantt").colums(9).TextToColumns , , , , -1, 0, 0, 0
End Sub

When I type sheets vba change to Sheets

In both lines, VBA displays
Excel VBA, error 438 "object doesn't support this property or method.

Why in this code IntelliSense is not working

Moreover, this is my first time I see ", , , ,-1,0,0,0"

I really appreciate advice on how to fix this problem.

Cheers

[mancubus]

https://docs.microsoft.com/en-us/off....texttocolumns

[JKwan]

try this:

Sub M_snb()    Dim ws As Worksheet
    Set ws = Worksheets("qry_creategantt")


    ws.Columns(9).TextToColumns , , , , -1, 0, 0, 0
End Sub

[Aflatoon]

To amend your original:

Sub textTonumber()Dim ws As Worksheet
Set ws = Worksheets("qry_creategantt")
        
    ws.Range("I2", ws.Range("I2").End(xlDown)).TextToColumns Destination:=ws.Range("I2"), DataType:=xlDelimited, _
        TextQualifier:=xlTextQualifierDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Other:=False, FieldInfo:=Array(1, 1), TrailingMinusNumbers:=True


End Sub

or skip the variable:

Sub textTonumber()
    With Worksheets("qry_creategantt")
        .Range("I2", .Range("I2").End(xlDown)).TextToColumns Destination:=.Range("I2"), DataType:=xlDelimited, _
            TextQualifier:=xlTextQualifierDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
            Semicolon:=False, Comma:=False, Other:=False, FieldInfo:=Array(1, 1), TrailingMinusNumbers:=True
    End With
End Sub

[sabael]

JKwan.
With only two lines, your code works as I need.
Thank you

[snb]

Sub M_snb()
   sheets("qry_creategantt").columns(9).TextToColumns ,,,, -1,0, 0, 0 
End Sub

[sabael]

snb.
I apologize, I did not read your code with due care.
Editing column to columns the code works fine.

Thank you