Creating a Text Sentence using a number of Variables in Excel VBA

[CBrowne]

I hope someone can help me with this. This is the background:

A LOT contains 1-6 unique Plots. The user can select any one of the plots and any number of them.

______LOT 999_____

| 1 | 2 | 3 | 4 | 5 | 6 |

For instance, the user may have selected plots 1, 3, 5, or maybe just 6, and so on.

For each of the 6 plots, I have a Boolean variable, true or false.



Dim SaveLot as Integer

Dim SelectedPlot1 as Boolean

Dim SelectedPlot2 as Boolean

Dim SelectedPlot3 as Boolean

Dim SelectedPlot4 as Boolean

Dim SelectedPlot5 as Boolean

Dim SelectedPlot6 as Boolean

Dim HoldDesc as ‘to hold the final text sentence



I want to create a text sentence like “Lot 999, Plots 1, 3, 5” or “Lot 999, Plot 1” or “Lot 999, Plots 4, 6”.

I am a fairly new beginner with VBA, and I’ve used every iteration of If, And, Or, Else, Elseif, but no matter how hard I try I cannot solve this.

Any help would be appreciated!

 

[rlv01]

It's mainly using string concatenation. One example:

Sub LotSentence()
    Dim SaveLot As Integer, I As Integer
    Dim SelectedPlot1 As Boolean
    Dim SelectedPlot2 As Boolean
    Dim SelectedPlot3 As Boolean
    Dim SelectedPlot4 As Boolean
    Dim SelectedPlot5 As Boolean
    Dim SelectedPlot6 As Boolean
    Dim HoldDesc As String                            'to hold the final text sentence
    
    Dim PlotArr As Variant
    Dim PlotCnt As Integer

    'Sample data
    SaveLot = 999

    SelectedPlot1 = True
    SelectedPlot2 = False
    SelectedPlot3 = True
    SelectedPlot4 = True
    SelectedPlot5 = True
    SelectedPlot6 = False

    'Create plot array
    PlotArr = Array(SelectedPlot1, SelectedPlot2, SelectedPlot3, SelectedPlot4, SelectedPlot5, SelectedPlot6)
 
    'Build string
    HoldDesc = "Lot " & SaveLot & ", Plots "

    For I = 0 To UBound(PlotArr)
        If CBool(PlotArr(I)) Then
            PlotCnt = PlotCnt + 1
            HoldDesc = HoldDesc & I + 1 & ","
        End If
    Next I

    HoldDesc = Left(HoldDesc, Len(HoldDesc) - 1)

    Select Case PlotCnt
    Case 0
        HoldDesc = "Lot " & SaveLot & " has no plots selected"
    Case 1
        HoldDesc = Replace(HoldDesc, "Plots", "Plot")
    End Select

    MsgBox HoldDesc
End Sub

 

[CBrowne]

That worked perfectly! Thank you sooo much.
Now I need to study your answer so I understand it and can repeat it on my own.

 

[rlv01]

Welcome to the forum.