Text to Columns (60 columns)

[luckycharm1668]

Hi, I am trying to text to column 60 columns, sometimes more. Could someone please help? Is there a code that I can write? I tried to record, but it's not working correctly. I also need to Unprotect the sheet and workbook, and insert 4 lines. Your assistance will be greatly appreciated.

Thank you.

 

[luckycharm1668]

Hi Erik,

I enable the lines and the macro ran without any errors ... YAY!!!

But when I right click a number cell or a percentage cell, it still shows those cell as "General", not "Number" or "Percentage" representation of the cell. Because when I try to do a vlookup, it would not calculate properly.

After I export this report, and getting every column to "Number", "Number" to one decimal place, and "Percentage" to one decimal place formats. Then I will be able to do some other calculations to find out their ratio to average. Is it possible to get these various columns in Number or Percentage format?

Thank you very much for your great assistance.

 

[erik.van.geit]

I know that the format part was not ok yet, but my macro was considered as a "start".

Try something like this

Option Explicit
 
Sub test()
'Erik Van Geit
'code will BUG if some cells are containing errors like #NAME, #ISNA, etcetera, enable 'On Error'lines
Dim rng As Range
Dim arr As Variant
Dim i As Long
Dim r As Long
Dim ColArr As Variant
Dim FormatArr As Variant
Dim LR As Long
 
'EDIT
'put all columns in the array
ColArr = Array(2, 4, 7)
FormatArr = Array("General", "0.00", "0.00%")
'define first row
Const FR = 2
'END EDIT

    With ActiveSheet.Cells
    LR = .Find("*", .Cells(1, 1), xlFormulas, xlPart, xlByRows, xlPrevious, False, False).Row
    End With
    
    'On Error Resume Next
    For i = 0 To UBound(ColArr)
    Set rng = Range(Cells(FR, ColArr(i)), Cells(LR, ColArr(i)))
    arr = rng
    
        For r = 1 To UBound(arr)
        arr(r, 1) = --arr(r, 1)
        Next r
        With rng
        .NumberFormat = FormatArr(i)
        .Value = arr
        End With
    Next i
    'On Error GoTo 0
Erase arr
End Sub

Think about editing the code the way you need it.

If possible, I would avoid to enable "on error resume next", at least while testing.

Getting closer?

best regards,
Erik

 

[luckycharm1668]

Hi Erik, Thank you for your great effort in keep helping me out. I still have so much to learn about VBA codes as I don't quite understand exactly what each line is performing. I put in your new codes, but I still get stuck at arr(r,1) = --arr(r,1) if I don't enable the "on error resume next".

I am very thankful for you going out of your way to help me.
Wishing you a wonderful day.

 

[erik.van.geit]

please email me the file: perhaps this will clear up misunderstanding

make it one sheet and not to big
subject line = subject of this thread
include link to this thread
include purpose of the code
like: column B: should become numbers, format "0.00"
etcetera

 

[erik.van.geit]

file recieved
what is you decimal separator?

I suspect that you need the comma instead of the dot
if that is the case, then replace commas by dots and see where you get: then report please

 

[luckycharm1668]

Hi Erik, I changed the comma instead of dot, I still get stuck at arr(r,1) = --arr(r,1). Kindly please advise.

Thank you.

 

[erik.van.geit]

you didn't answer my question (what is you decimal separator?)
+
did you try "replace"??

 

[luckycharm1668]

I am very sorry, but I don't understand the question.
There is no decimal separator. The format for the certain columns need to be to one decimal place, not a decimal separator.

I did not use replace.

Thank you for your assistance.

 

[erik.van.geit]

60.12
60,12

the dot or the comma can be decimal separator
which one do you have?

What do you get when you type a DOT from the numerical keypad?
Do you get a DOT as in the first example or a COMMA as you can see in the second?

 

[luckycharm1668]

Hi, When I type a dot on my keypad, I get a dot as in the first example.
60.12

Thank you for your assistance.