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:

    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

  • Re: Concanetate Or User Input, And Filldown

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

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

Participate now!

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