how do I pass an array into range().texttocolumns?

jvantassel

New Member
Joined
Apr 28, 2021
Messages
13
Office Version
  1. 2016
Platform
  1. Windows
I want to be able to select a range of data, and use text-to-columns to convert the data to text, too often we have codes that are numeric stored as text and sometimes loose the leading zeros, not to mention formatting issues.

Since the range(x,y).texttocolumns can only use a one dimentional array, I thought I could select a 2-dimentional array with an input box, then pass it into an array with the text-to-colmuns method, then over write the existing cells with the values.

I’m able to fill an array, and write to cells on the sheet, yet finding that I’m challenged with trying to figure out how to combine the array and the range().texttocolumns.

Would you help me?

**********************************************************

Sub Macro2()

Dim myArray()

Dim dim1 As Long, dim2 As Long

Dim mySelection As Variant

'select the area to convert to text

mySelection = myInputbox()

'determine the number of rows and columns

dim1 = Range(mySelection).Rows.Count

dim2 = Range(mySelection).Columns.Count



'resize the array

ReDim myArray(1 To dim1, 1 To dim2)



'fill the array

For dim1 = LBound(myArray, 1) To UBound(myArray, 1) 'loop the rows

For dim2 = LBound(myArray, 2) To UBound(myArray, 2) 'loop the column within the row

myArray(dim1, dim2) = Range(mySelection).Offset(dim1 - 1, dim2 - 1).Value

Next dim2

Next dim1

'fill the worksheet

For dim1 = LBound(myArray, 1) To UBound(myArray, 1) 'loop the rows

For dim2 = LBound(myArray, 2) To UBound(myArray, 2) 'loop the column within the row

Range("G1").Offset(dim1 - 1, dim2 - 1).Value = myArray(dim1, dim2)

Next dim2

Next dim1


'Here is where I was playing with my texttocolumns method

'Range(rangeHeader).TextToColumns Range(rangeHeader), DataType:=xlDelimited, TextQualifier _

' :=xlDoubleQuote, Tab:=True, FieldInfo:=Array(1, 2), TrailingMinusNumbers:=True

'Range(rangeHeader).HorizontalAlignment = xlLeft





End Sub
 

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.
@jvantassel If you just want to be able to convert ranges to text, why not just use something like the following: Or did I miss something?

VBA Code:
Sub Convert_To_Text()
    Dim vData As Variant
'
    StartRange = "A1"                                       ' Change this to desired Start range
    EndRange = "C20"                                        ' Change this to desired End range
'
    vData = Range(StartRange & ":" & EndRange)
    Range(StartRange & ":" & EndRange).NumberFormat = "@"
    Range(StartRange & ":" & EndRange) = vData
End Sub
 
Upvote 0
@jvantassel If you just want to be able to convert ranges to text, why not just use something like the following: Or did I miss something?

VBA Code:
Sub Convert_To_Text()
    Dim vData As Variant
'
    StartRange = "A1"                                       ' Change this to desired Start range
    EndRange = "C20"                                        ' Change this to desired End range
'
    vData = Range(StartRange & ":" & EndRange)
    Range(StartRange & ":" & EndRange).NumberFormat = "@"
    Range(StartRange & ":" & EndRange) = vData
End Sub
Thanks for your response, I found that the texttocolumns is the most reliable method, even highlighting a row and changing to text proves unreliable, as did adding an apostrophe. I do have a macro written that works for a one dimensional array, i.e., single column. I'll play with this and see.
 
Upvote 0
Thanks for your response, I found that the texttocolumns is the most reliable method, even highlighting a row and changing to text proves unreliable, as did adding an apostrophe. I do have a macro written that works for a one dimensional array, i.e., single column. I'll play with this and see.
I tested the code I submitted for numbers, numbers that start with '0.', leading spaces, spaces in the middle, etc, they all came back as text results . Try it and report any problems you may encounter.
 
Upvote 0

Forum statistics

Threads
1,213,560
Messages
6,114,309
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top