rschmidt46
New Member
- Joined
- Aug 24, 2015
- Messages
- 46
- Office Version
- 2010
- Platform
- Windows
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.
VBA Code:
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
Last edited by a moderator: