jvantassel
New Member
- Joined
- Apr 28, 2021
- Messages
- 13
- Office Version
- 2016
- Platform
- 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
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