CODEWITHSUNDEEP
vbaexcel
user3376237
user3376237

Reputation: 27

Excel VBA format a series of months with commas and a "and" at the end

so I have a several non-sequential rows in excel that have a certain month (I.e. C3 = "January", F3 = "October" and H3 = "December). I haven't been able to figure out how to move all of these strings into one cell with VBA and convert them into the following string

"January, October, and December"

Adding a comma between all of them has been doable but converting that final , to an "and" has been the issue.

The amount of months that are populated in cells b3:m3 are populated randomly depending on a different formula and range from 1 to 4 different months depending on the value of another row.

Any is help is appreciated!

Upvotes: 0

Views: 144

Answers (1)

Automate This
Automate This

Reputation: 31394

Starting with data like this:

enter image description here

Run this code:

Sub months()
    Dim wks As Worksheet
    Dim rng As Range

    Set wks = Worksheets("Sheet4")
    Set rng = wks.Range("B3:M3")

    Dim count As Integer
    count = WorksheetFunction.CountA(rng)
    If count < 1 Then Exit Sub

    Dim result As String

    For Each cell In rng
        If Not IsEmpty(cell) Then
            result = result & cell.Value

            Select Case UBound(Split(result, ","))
                Case count - 2
                    result = result & ", and "
                Case count - 1
                Case Else
                    result = result & ", "
            End Select
        End If
    Next cell

    MsgBox result
End Sub

Result:

enter image description here

Note: You can still salvage your question (keep it from getting closed) by editing the original post and including your other requirements that you listed in the comments. Also, any screen shots etc to help define all of your requirements should be added as well.

Upvotes: 1

Related Questions