Left Function using activecell.offset

cdukes

New Member
Joined
Jul 26, 2010
Messages
8
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
 
Last edited:

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".

VoG

Legend
Joined
Jun 19, 2002
Messages
63,650
Try

Code:
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

MrExcel MVP
Joined
Sep 2, 2009
Messages
16,416
Or something like this. It uses Text-to-Columns and the space is the delimiter

Code:
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

New Member
Joined
Jul 26, 2010
Messages
8
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
 

Watch MrExcel Video

Forum statistics

Threads
1,133,325
Messages
5,658,174
Members
418,430
Latest member
Chlwls808

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top