PDA

View Full Version : [SOLVED] Text to column macro



peteywarner
10-30-2007, 02:22 AM
Hi, I was hoping someone may be able to help me here.

I am trying to add to a macro script, a part that will convert text to columns. Currently I run a small macro that sorts the data and deletes what I don't want, and then it uses formulae in excel cells to manipulate the data into columns. I wanted if possible to get the macro to do all of it, as excel falls over once I reach about 25,000 lines; due to the number of formulae involved (excel 2000). From the one text field I extract fourteen different columns, obviously each formulae is different mostly due to Left, Mid and Right combinations; but there are other varying IF statements also.

Thanks in advance.

Pete :help

Bob Phillips
10-30-2007, 02:47 AM
Do it in Excel with the macro recorder on. You will have your code.

peteywarner
10-30-2007, 02:55 AM
Thanks for the quick response; but sorry I'm not sure I understand, do what with the recorder on? run my existing macro? type in the formula?

Bob Phillips
10-30-2007, 03:26 AM
To do the text to columns bit, then add that code to your existing.

peteywarner
10-30-2007, 04:56 AM
Hi, sorry I probably didn't explain myself well enough, I don't actually use the text to column function as my knowledge of VBA is very basic, and I couldn't work out how to join certain columns on only some of the rows back up again based on certain criteria within the macro, so I have done it with "IF" statements in excel cells, I was wondering if I could just add these "IF" statements into the macro.

Thanks

Pete

Bob Phillips
10-30-2007, 05:16 AM
This is a bit like fishing in the dark, but I guess it would be something like


Public Sub ProcessData()
Const TEST_COLUMN As String = "A" '<=== change to suit
Dim i As Long
Dim iLastRow As Long
Const delim As String = ", " 'change to suit
With ActiveSheet
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
iLastRow = .Cells(.Rows.Count, TEST_COLUMN).End(xlUp).Row
For i = 2 To iLastRow
.Cells(i, "F").Value = .Cells(i, "A") & delim & _
.Cells(i, "B") & delim & _
.Cells(i, "C") & delim & _
.Cells(i, "D") & delim & _
.Cells(i, "E")
Next I
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End With
End Sub

peteywarner
10-30-2007, 06:45 AM
Cool thanks for that I will have a play around with it, thanks for all your help.