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

 

[johnnyL]

I think you should supply some sample data because the code is in need of some major help.

 

[portews]

I agree with johnnyL, if you can post at least the header rows, that would help. Use the XL2BB button at top or Upload Mini-Sheet at the bottom.

Here's my shot at it, without seeing the table (not totally tested). My advice is to built your sentence as you go. If there is a "Yes" in Choir or Ensemble, build a string variable with their name then loop through each column to see what instrument they play. See if this works for you. It will pop up with a message box for each person.

Sub Test_For_Multiple_Instruments()
Dim What As String
Dim Choir As Integer
Dim Ensemble As Integer
Dim counter As Integer

Choir = 4 'set this to the Choir column
Ensemble = 5 'set this to the Ensemble column

For Q = 10 To 40 'for each person
counter = 0 'fresh count for everybody
        'look for a yes in either Choir or Ensemble column
        If Cells(Q, Choir).Value = "Yes" Or Cells(Q, Ensemble).Value = "Yes" Then
            'build the message as you go.  Start with the name
            What = Cells(Q, 2) & " " & Cells(Q, 1) & " plays "
            'loop through the columns
            For T = 29 To 50
            'if the cell in that row has a yes...
            If Cells(Q, T) = "Yes" Then
                'how many different thing do they play?
                counter = counter + 1
                'put in a comma after the first one
                If counter > 1 Then What = What & ","
                'grab the column header and add it to the string
                What = What & Cells(1, T).Value
            End If
        End If
            'change the last comma to "and"
            What = WorksheetFunction.Replace(What, InStrRev(What, ","), 1, " and")
            MsgBox (What)
    'next instrument
    Next T
'next person
Next Q

 

[rschmidt46]

I'm not sure I know what you want. Q is the variable for row. The outer loop goes from 20 to 40. T is the variable for the column. The inner loop goes from column 29 to 50, looking for "Yes" in a cell. When it finds the first "Yes" as it would for a person who plays flute (in row 23, column 35), I need a variable that contains "Flute" and isn't reset when the inner loop looks to subsequent columns to find another cell in the same row that contains "Yes" as it would if that person also plays oboe (in row 23, column 39). I need to retain two values for later use in code, one containing "flute", the other "Oboe".

 

[Peter_SSs]

@rschmidt46
I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Also, when posting vba code in the forum, please use the available code tags. It makes your code much easier to read/debug. My signature block below has more details. I have added the tags for you this time.

 

[johnnyL]

Then, depending on what the person sings or plays, there are columns for each type of musical ability the person lists.

Are we supposed to guess what those columns are, or wait for your follow up questions on how to handle those columns? It would be much simpler to help you if you provided sample data.

Your choice.

 

[rschmidt46]

Sample of Data Base for Posting on Mr Excel.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH	AI	AJ	AK	AL	AM	AN	AO	AP	AQ	AR	AS	AT	AU	AV	AW	AX	AY	AZ	BA	BB	BC	BD	BE	BF	BG	BH	BI	BJ	BK	BL	BM
1	Last Name	First Name	Email	Phone Number	Gender	Date of Birth	Age	Congregation Normally Attend	Adult Choir	Children's Choir	Teen Choir	Adult Choir Director	Children's Choir Director	Teen Choir Director	Vocal Solo	Pianist for Choir	Pianist for Hymns	Instrumental Ensemble	Instrumental Solo	Small Group	Performing outside of services	Soprano	Alto	Tenor	Baritone	Bass	Vocal Part Other	Other Music Part	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	Instrumental Director	Instrumental Part Other	Other Instrumental Part	Instrumental Music Experience	Performing Outside of Services	Small Group	Vocal Music Experience	Prior Sites Person Performed	Notes for the Music Coordinator	Street Address	Street Address 2	City	State	ZIP/Postal Code	Country
2	Blow	Joe							Yes									Yes	Yes						Yes										Yes				Yes
3	Smith	Suzie																Yes																Yes																Yes
Sheet1

 

[johnnyL]

There ya go. Thank you for that.

 

[rschmidt46]

I attached the actual column headings and two rows of sample data to show what I'm working with. I need to capture both "Flute" and "Oboe" for Joe Blow and "Clarinet" and "Violin" for Suzie Smith.

 

[rschmidt46]

I have not tried PORTEWS code yet.