Concanetate Or User Input, And Filldown

    My cellular carrier supplies my monthly commission reports where column "S" indicates the month and column "T" indicates the year. The first few hundred rows will indicate the month like "NOV" and the reminding rows will have it like "11".


    The year will always be yyyy.


    I want to combine these into one column where it will read like "NOV, 2006" so it will match other months already entered.


    Each month has its own worksheet.


    I started to concanetate and fill down all the active rows.
    I found this snippit of code but cannot get it to work:

    Code
    Range("U1").Select
ActiveCell.FormulaR1C1 = "=RC[-2]&"", ""&RC[-1]"
ActiveCell.Range("U1:U" & LastMSRow).FillDown


    But I got to thinking,..Should I open an InPutBox? or something and have the user just type in what they want. This would solve the "NOV" and "11" issue. But I'm still left with xlFillDown? and I don't know how to get it to stop when it finds an empty row.


    Hope I'm not breaking forum rules by asking 2 questions?
    1) Should I use the concanetate & fill down or require user input?
    2) How do I get the auto fill down to work as needed?


    Thanks,
    Charles




    Thanks, Charles

    Re: Concanetate Or User Input, And Filldown


    Something like this sounds liek it should do what you are after.

    Code
    Sub MakeMonthsAlpha()
Dim c As Range
For Each c In Range(Cells(1, 19), Cells(Rows.Count, 19).End(xlUp))
    If Not IsNumeric(c) Then
            Cells(c.Row, 21) = c.Value & ", " & c.Offset(0, 1)
        Else
            Cells(c.Row, 21) = UCase(MonthName(c, True)) & ", " & c.Offset(0, 1)
    End If
Next c
End Sub

    Re: Concanetate Or User Input, And Filldown


    Thanks! That works like a charm.
    AND I was able to examine the code and get an understanding of HOW it does it.


    Thank you again,
    Charles

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login