Keep First Value of Variable in a Loop but not Exiting the Loop

[rschmidt46]

I have a spreadsheet with 66 columns and 64 rows. Each row represents a person who registered to participate in musical service for a church festival. Some people sing and some play an instrument. Some do both. And some play more than one instrument. If a person sings, the spreadsheet has a "Yes" in the column labeled "Choir". If someone plays an instrument there is a "Yes" in the column labeled "Ensemble". Then, depending on what the person sings or plays, there are columns for each type of musical ability the person lists. Columns 29 to 50 are labeled with different instruments. My problem is capturing the data when a person plays two instruments. I am hung up on how to set Variable Inst1 to "Flute" when the code encounters a "Yes" in that column and then retain it while the code searches for the second instrument ("Oboe") and sets Variable Inst2 as "oboe". I'm sure there's simple solution to this and I'm just overlooking it. The MsgBox line is a test (which will be replaced with additional code once it proves out) that is supposed to say "Joe Blow plays flute and oboe." ("Joe" is in column 2 and "Blow" is in column 1.) I am only testing on rows 10 to 40 because the people whose information is on row 11 and 39 play two instruments.

Sub Test_For_Multiple_Instruments()
For Q = 10 To 40
Count = 0
For T = 29 To 50
    If Cells(Q, T).Value = "Yes" Then Count = Count + 1: Inst1 = Cells(1, T).Value
        If Count > 1 Then
        Inst2 = Cells(1, T).Value
        MsgBox (Cells(Q, 2).Value & " " & Cells(Q, 1).Value & " plays " & Inst1 & " " & Inst2 & ".")
    End If
Next T
Next Q
End Sub

 

[Peter_SSs]

I'm not sure of your ultimate goal, but I'm wondering if you even need a macro?

Could something along these lines be any use?

rschmidt46.xlsm
	A	B																											AC	AD	AE	AF	AG	AH	AI	AJ	AK	AL	AM	AN	AO	AP	AQ	AR	AS	AT	AU	AV	AW	AX
1	Last Name	First Name																											Alto Saxophone	Baritone Saxophone	Bass Guitar	Bassoon	Cello	Clarinet	Flute	French Horn	Guitar	Harp	Oboe	Organ	Percussion	Piano/Keyboard	Piccolo	String Bass	Tenor Saxophone	Trombone	Trumpet/Cornet	Tuba	Viola	Violin
2	Blow	Joe																																	Yes				Yes
3	Smith	Suzie																																Yes																Yes
4	Doe	Jane
5
6
7	Joe Blow plays Flute and Oboe
8	Suzie Smith plays Clarinet and Violin
9	Jane Doe plays nothing
Sheet1

Cell Formulas
Range	Formula
A7:A9	A7	=B2&" "&A2&" plays "&IFNA(TEXTJOIN(" and ",1,FILTER(AC$1:AX$1,AC2:AX2="Yes",NA())),"nothing")

 

[johnnyL]

On a side note, when did @Rick Rothstein take a new profile pic and change the nick to @rschmidt46?

 

[rschmidt46]

Hi Peter,
The macro is needed because it's to sort the records. I'm assigning numeric values to voice parts and instruments. Once I've verified that the code works as indicated by the output of a message box, I'll replace that with inserting numeric values in a new column. Then I'll copy the worksheet and then sort it by part value. I have the scheme for the numeric values figured out. I got hung up on how to identify records with "Yes" in multiple columns.
Randy

 

[Peter_SSs]

The macro is needed ..

OK, then for my sample data and layout above, try (no looping columns) ..

Sub Test()
  Dim r As Long
  Dim x
  
  For r = 2 To 4
    x = Replace(Join(Filter(Evaluate("AC1:AX1&""|""&AC" & r & ":AX" & r), "|Yes"), " and "), "|Yes", "")
    MsgBox Cells(r, 2).Value & " " & Cells(r, 1).Value & " plays " & IIf(Len(x) = 0, "nothing", x)
  Next r
End Sub

 

[rschmidt46]

Peter,
This code works! I think I can adapt it as needed.
Thank you,
Randy

 

[johnnyL]

My attempt was not as short as @Peter_SSs, but here it is:

Sub Test_For_Multiple_Instruments()
'
    Dim Count               As Long
    Dim InstrumentColumn    As Long
    Dim LastRow             As Long
    Dim ColumnNumber        As Long
    Dim RowNumber           As Long
    Dim StartRowOfData      As Long
    Dim FirstNameColumn     As String
    Dim Inst1               As String, Inst2    As String
    Dim LastNameColumn      As String
    Dim StringToDisplay     As String
    Dim WS                  As Worksheet
'
    Set WS = Sheets("Sheet1")                                               ' <--- Set this to the Source Sheet name to use for the data
    LastNameColumn = 1
    FirstNameColumn = 2
    StartRowOfData = 2
'
    LastColumnInSheet = Split(Cells(1, (WS.Cells.Find("*", , xlFormulas, , xlByColumns, xlPrevious).Column)).Address, "$")(1)   ' Get last used Column Letter in source sheet
    LastRow = Range("A" & Rows.Count).End(xlUp).Row                         ' Get last used row in column A

    SourceArray = WS.Range("A" & StartRowOfData & ":" & LastColumnInSheet & LastRow)    ' Load Source data to SourceArray
'
''    For RowNumber = 10 To 40
    For RowNumber = LBound(SourceArray, 1) To UBound(SourceArray, 1)        ' Loop through rows of SourceArray
        Count = 0
'
        For ColumnNumber = 29 To 50                                         '   Loop through Instrument columns of SourceArray
            If SourceArray(RowNumber, ColumnNumber) = "Yes" Then
                Count = Count + 1
'
                If Inst1 = "" Then
                    Inst1 = SourceArray(RowNumber, ColumnNumber)
                ElseIf Inst2 = "" Then
                    Inst2 = SourceArray(RowNumber, ColumnNumber)
                End If
            End If
        Next
'
        Select Case Count
            Case 0: StringToDisplay = SourceArray(RowNumber, FirstNameColumn) & " " & SourceArray(RowNumber, LastNameColumn) & " Doesn't play any instruments."
            Case 1: StringToDisplay = SourceArray(RowNumber, FirstNameColumn) & " " & SourceArray(RowNumber, LastNameColumn) & " plays " & Inst1 & "."
            Case 2: StringToDisplay = SourceArray(RowNumber, FirstNameColumn) & " " & SourceArray(RowNumber, LastNameColumn) & " plays " & Inst1 & " and " & Inst2 & "."
        End Select
'
        MsgBox StringToDisplay
    Next RowNumber
End Sub

 

[Peter_SSs]

Peter,
This code works! I think I can adapt it as needed.
Thank you,
Randy

You're welcome. Thanks for the follow-up.

 

[rschmidt46]

Hi Johnny,
This code produces "Joe Blow plays yes". Peter's code does work for me and I will adapt is as necessary. Thank you for getting back to me. All of you have been really helpful.
Randy

 

[Peter_SSs]

Actually, we could also have just converted the formula that I suggested earlier

Sub Test2()
  Dim r As Long

  For r = 2 To 4
    MsgBox Evaluate(Replace("B#&"" ""&A#&"" plays ""&IFNA(TEXTJOIN("" and "",1,FILTER(AC$1:AX$1,AC#:AX#=""Yes"",NA())),""nothing"")", "#", r))
  Next r
End Sub

 

[johnnyL]

Hi Johnny,
This code produces "Joe Blow plays yes". Peter's code does work for me and I will adapt is as necessary. Thank you for getting back to me. All of you have been really helpful.
Randy

Sorry, I should have tested it instead of just posting what I came up with in my head.

Here is a tested version:

Sub Test_For_Multiple_Instruments()
'
    Dim Count                   As Long
    Dim LastRow                 As Long
    Dim ColumnNumber            As Long, RowNumber          As Long
    Dim FirstNameColumn         As String, LastNameColumn   As String, LastColumnInSheet    As String
    Dim Inst1                   As String, Inst2            As String
    Dim StringToDisplay         As String
    Dim SourceArray             As Variant
    Dim WS                      As Worksheet
'
    Set WS = Sheets("Sheet1")                                               ' <--- Set this to the Source Sheet name to use for the data
    LastNameColumn = 1                                                      ' Set LastNameColumn #
    FirstNameColumn = 2                                                     ' Set FirstNameColumn #
'
    LastColumnInSheet = Split(Cells(1, (WS.Cells.Find("*", , xlFormulas, _
            , xlByColumns, xlPrevious).Column)).Address, "$")(1)            ' Get last used Column Letter in source sheet
    LastRow = Range("A" & Rows.Count).End(xlUp).Row                         ' Get last used row in column A

    SourceArray = WS.Range("A1:" & LastColumnInSheet & LastRow)             ' Load Source data to SourceArray
'
    For RowNumber = 2 To UBound(SourceArray, 1)                             ' Loop through rows of SourceArray
        Count = 0                                                           '   Initialize Count
'
        For ColumnNumber = 29 To 50                                         '   Loop through Instrument columns of SourceArray
            If SourceArray(RowNumber, ColumnNumber) = "Yes" Then            '       If Instrument column value = "Yes" then ...
                Count = Count + 1                                           '           Increment Count
'
                If Inst1 = vbNullString Then                                '           If Inst1 is blank then ...
                    Inst1 = SourceArray(1, ColumnNumber)                    '               Save the Instrument name into Inst1
                ElseIf Inst2 = vbNullString Then                            '           If Inst2 is blank then ...
                    Inst2 = SourceArray(1, ColumnNumber)                    '               Save the Instrument name into Inst2
                End If
            End If
        Next                                                                '   Loop back
'
        Select Case Count
            Case 0: StringToDisplay = SourceArray(RowNumber, FirstNameColumn) & _
                    " " & SourceArray(RowNumber, LastNameColumn) & _
                    " Doesn't play any instruments."                        '       Indicate no instruments are played
            Case 1: StringToDisplay = SourceArray(RowNumber, FirstNameColumn) & _
                    " " & SourceArray(RowNumber, LastNameColumn) & _
                    " plays " & Inst1 & "."                                 '       Indicate the first instrument played
            Case 2: StringToDisplay = SourceArray(RowNumber, FirstNameColumn) & _
                    " " & SourceArray(RowNumber, LastNameColumn) & _
                    " plays " & Inst1 & " and " & Inst2 & "."               '       Indicate the first & second instrument played
        End Select
'
        Inst1 = vbNullString                                                '   Erase Inst1
        Inst2 = vbNullString                                                '   Erase Inst2
'
        MsgBox StringToDisplay                                              '   Display message to user
    Next RowNumber                                                          ' Loop back
End Sub