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:

Some videos you may like

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"

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,271
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
 

Subscribe on YouTube

Watch MrExcel Video

Forum statistics

Threads
1,106,218
Messages
5,509,886
Members
408,760
Latest member
DoktorPhill

This Week's Hot Topics

  • Turn fraction around
    Hello I need to turn a fraction around, for example I have 1/3 but I need to present as 3/1
  • TIme Clock record reformatting to ???
    Hello All, I'd like some help formatting this (Tbl-A)(Loaded via Power Query) [ATTACH type="full" width="511px" alt="PQdata.png"]22252[/ATTACH]...
  • TextBox Match
    hi, I am having a few issues with my code below, what I need it to do is when they enter a value in textbox8 (QTY) either 1,2 or 3 the 3 textboxes...
  • Using Large function based on Multiple Criteria
    Hello, I can't seem to get a Large formula to work based on two criteria's. I can easily get a oldest value based one value, but I'm struggling...
  • Can you check my code please
    Hi, Im going round in circles with a Compil Error End With Without With Here is the code [CODE=rich] Private Sub...
  • Combining 2 pivot tables into 1 chart
    Hello everyone, My question sounds simple but I do not know the answer. I have 2 pivot tables and 2 charts that go with this. However I want to...
Top