Left Function using activecell.offset

[cdukes]

Hello:

I am attempting to separate the contents of a column of cells using the "left" function. Specifically, I have a column of data such as "2Q 2010" that I need to split into a column for the quarter and a column for the year. I have constructed a Do...While loop to perform this function as long as there are row entries.

My data is constructed as follows:
OA | OB | OC
1 QTR/Yr | Quarter | Year
2 2Q 2010

Below is my code so far, it runs without error, but does not produce the desired values in the Quarter and Year columns. I would appreciate any suggestions.

Range("OB2").Activate

Do While IsEmpty(ActiveCell.Offset(0, 1)) = False
ActiveCell.Formula = "Left(1,ActiveCell.Offset(0,-1))"
ActiveCell.Offset(1, 0).Select
Loop

Range("OC2").Activate

Do While IsEmpty(ActiveCell.Offset(0, 1)) = False
ActiveCell.Formula = "Right(4,ActiveCell.Offset(0,-2))"
ActiveCell.Offset(1, 0).Select
Loop

Thanks for any help,
cdukes

 

[VoG]

Try

Sub Splt()
Dim LR As Long, i As Long
LR = Range("OB" & Rows.Count).End(xlUp).Row
For i = 2 To LR
    With Range("OB" & i)
        .Resize(, 2).Value = Split(.Value)
    End With
Next i
End Sub

 

[AlphaFrog]

Or something like this. It uses Text-to-Columns and the space is the delimiter

Sub Test()

    Dim rng As Range
    
    Application.ScreenUpdating = False
    
    Set rng = Range("OA2", Range("OA" & Rows.Count).End(xlUp))
    
    rng.Copy Range("OB2")
    
    rng.Offset(, 1).TextToColumns Destination:=rng.Offset(, 1), DataType:=xlDelimited, _
                                  TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=1, Tab:=0, _
                                  Semicolon:=0, Comma:=0, Space:=1, Other:=0, TrailingMinusNumbers:=True
    
    Application.ScreenUpdating = True
    
End Sub

 

[cdukes]

Thanks, AlphaFrog. That worked quite well.

 

[cdukes]

This too:

Dim i As Integer
Dim q As Integer
Dim FinalRow As Integer

FinalRow = Cells(Rows.Count, 1).End(xlUp).Row


For q = 2 To FinalRow
Cells(q, 392).Activate

ActiveCell.FormulaR1C1 = "=Left(R[0]C[-1],1)"

Cells(q, 393).Activate

ActiveCell.FormulaR1C1 = "=Right(R[0]C[-2],4)"

Next q