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
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
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
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.