Make me faster!!!!!

[pooley100777]

Can anyone help with the following, i did post earlier but it was on another subject.

I have the following code:

Sheets("MCA CODES").Select
Range("D1").Select
Do
ActiveCell.FormulaR1C1 = "=MID(RC[-1],5,3)"
ActiveCell.Offset(0, 1).Select
ActiveCell.FormulaR1C1 = "=MID(RC[-2],8,2)"
ActiveCell.Offset(0, 1).Select
ActiveCell.FormulaR1C1 = "=MID(RC[-3],10,2)"
ActiveCell.Offset(0, 1).Select
ActiveCell.FormulaR1C1 = "=MID(RC[-4],12,4)"
ActiveCell.Offset(1, -3).Select
Loop Until ActiveCell.Offset(0, -1) = ""
End Sub


Now the problem i have is that the spreadsheet has over 5000 lines and for each for line it has to pull from one cell 4 sets of data.

Example AAAASLSGNOTA1234 so in column D i would have SLS "E" GN "F" OT "G" A1234

I have done the formula above but it takes a good 20 minutes to run and i have already tried turning the screenupdating off but it hardly makes a difference... am i missing something simple or is there anyway of a workaround?

 

[Jonmo1]

Do really need the formula to end up in the cell, or just the resulting value?

This will give just the resulting values without the formula.

Sub Macro1()
With Sheets("MCA CODES")
    .Columns("D:D").TextToColumns Destination:=.Range("E1"), DataType:=xlFixedWidth, _
        FieldInfo:=Array(Array(0, 9), Array(4, 1), Array(7, 1), Array(9, 1), Array(11, 1), Array(16, 9))
End With
End Sub