PDA

View Full Version : [SOLVED] Avoid selection and select in format number



sabael
10-08-2018, 05:30 PM
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
10-09-2018, 02:15 AM
This is all you need:


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

sabael
10-09-2018, 06:35 AM
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
10-09-2018, 07:03 AM
https://docs.microsoft.com/en-us/office/vba/api/excel.range.texttocolumns

JKwan
10-09-2018, 07:06 AM
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
10-09-2018, 07:12 AM
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
10-09-2018, 07:36 AM
JKwan.
With only two lines, your code works as I need.:clap:
Thank you

snb
10-09-2018, 08:47 AM
Sub M_snb()
sheets("qry_creategantt").columns(9).TextToColumns ,,,, -1,0, 0, 0
End Sub

sabael
10-10-2018, 05:55 AM
snb.
I apologize, I did not read your code with due care. :(
Editing column to columns the code works fine.:clap:

Thank you