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.

 

[xenou]

You can select the cells that are text and format them as numbers/percents.

Then, if necessary,
1) Right click the cells and choose Copy from the right click menu
2) Right click again and choose Paste Special... from the right click menu
3) Paste Values

AB

Edit: Is this something you have to do every day?

 

[luckycharm1668]

Yes, I have to do this everyday to every column.
And there are at least 15 reports per day, up to 30 sometimes.

That's why I was hope to be able to write a VBA code for all these tedious repeating steps.

 

[xenou]

One final question...is this report as you posted about all of it or are there hundreds/thousands of more rows?

Oh, and ... Is this report basically always the same or does the layout and/or column headers change?

 

[luckycharm1668]

Yes, there are over thousand of rows.
Luckily the lay out is the same for the columns.
Except the Date and the name of the report.

Also, could you please consider helping me to right a code of the following tedious steps it take to export the report into Excel...Data\Planning Import Wizard\Next\Next\Next\Finish


Thank you very much in advance for your great assistance.

 

[xenou]

Also, could you please consider helping me to right a code of the following tedious steps it take to export the report into Excel...Data\Planning Import Wizard\Next\Next\Next\Finish

I haven't heard of this before. Is this an Excel 2007 wizard? What kind of data are you importing?

 

[luckycharm1668]

It's from an application called Arthur Planning, which can be export into Excel. And those are the step require to have the date exported.

Thank you.

 

[erik.van.geit]

We can not answer everything together, at least I can't

This would be my first try to do change the "test" values to "real" values.

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 LR As Long
'EDIT
'put all columns in the array
ColArr = Array(2, 4, 7)
'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
    rng = arr
    Next i
    'On Error GoTo 0
Erase arr
End Sub

best regards,
Erik

PS: I might be very irregular coming days

 

[northwolves]

Provide an txtfile example,I can help you.

 

[luckycharm1668]

Hi Erik,

Thank you very much for taking the time to help me write the incredible VBA code. I was so excited to try it out.

But I got an err msg at arr(r,1) = --arr(r,1)
Could you please let me know what that does?

Your assistance is greatly, greatly appreciated.

Thank you.

 

[erik.van.geit]

the problem is probably that you have "real text" in some cells
did you edit the first row line?
currently it is 2

enable the lines
'on error resume next
'on error goto 0

and see what you get