Declaring funktions value to variable

Hi,

I made a vba program which works fine. This program splits information from one cell into few cells. I think there is a better way to write the program. My program use formula, paste it into cell, calculate, then takes cell value (string) and paste it again into the cell. OK, this is fine, and it works while I dont have a lot of rows, but: I have 200.000 rows.

So my question is: is there a better way to declare variable?

All information is in column A and it looks like this:

20103335 18.08.2010 18.08.2010 268 30635866 42.00 PCE 8.06 338.52 25

I need a bold part in separate columns (Date 2, Buyer, Material, Volume, Value)

Question 2: Is there a better way to change column? I used "switch" command.
Question 3: I used if statement to jump over 7 and 8th loop sequence because I didnt need those informations, but I needed ninth. Is there a better way to do same thing?

This is the code:

Sub razbijac()

Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False


Dim Row As Long
Dim Column As String
Dim LastRow As Long
Dim CellValue As String


LastRow = Range("A" & Rows.Count).End(xlUp).Row


For i = 3 To 9

Column = Switch(i = 3, "C", i = 4, "D", i = 5, "E", i = 6, "F", i = 7, "G", i = 8, "H", i = 9, "G")

If i = 7 Or i = 8 Then

Else


        For Row = 1 To LastRow
        
        Range(Column & Row).Formula = "=Mid(A" & Row & ", Search(" & """@""" _
                   & ", Substitute(A" & Row & ", " & """ """ & ", " & """@""" & ", " & i - 1 & "), _
                   1) + 1, Search(" & """@""" & ", Substitute(A" & Row & ", " & """ """ & ", " _
                    & """@""" & ", " & i & "), 1) - Search(" & """@""" & ", Substitute(A" & Row _
                    & ", " & """ """ & ", " & """@""" & ", " & i - 1 & "), 1) - 1)"

        CellValue = Range(Column & Row).Text

        Range(Column & Row) = CellValue
        
                
        Next Row

End If

Next i


Application.Calculation = xlCalculationAutomatic

End Sub

Thanks in advance.