Populating a comments cell

[tmcshane224]

Hi there,<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o></o>
What is the best way to populate a comments cell based on answers selected? Also, how would I write the code for it?
<o></o>
Let’s say that cell <o></o>
A2 has an "a"<o></o>
B2 has an "a"<o></o>
C2 has an "a" and lastly <o></o>
D2 is blank<o></o>
<o></o>
F4 has a text comment "There are exposed electrical outlets in patient areas." related to A2, <o></o>
<o> </o>
G4 has a text comment "The fire extinguisher(s) need to be serviced. The last serviced date is __." related to B2 and <o></o>
<o> </o>
H4 has a text comment "was/were pleasant and helpful." related to D2<o></o>
<o></o>
I need the comment cell to have the text from F4 & G4 populated in it as they have been answered with an "a". <o></o>
<o></o>
C2 has been answered but there is no text for that answer.<o></o>
<o></o>
Lastly I need it to be separated by &", "&

Thanks<o></o>

 

[tmcshane224]

Correct, but my main goal is to get all comments in cell D101 no matter what question is answered.

A1-J1 is the questions with "a"

B2-B11 is the answers with "text"

So in the end we will have in D101: A1=B2 & C1=B4

 

[Desu Nota from Columbus]

Ok, so all comments are to be put into cell D101 separated by commas

 

[tmcshane224]

Yes

 

[Desu Nota from Columbus]

Try this macro: It uses helper cells in cells D102-D111:

Sub CombComments()
Dim r As Long

For r = 1 To 10
   If Cells(1, r) = "a" Then
      Cells(r + 101, 4).Formula = Cells(r + 1, 2)
   Else
   End If
Next r
Cells(101, 4).FormulaR1C1 = "=R[1]C& "", "" &R[2]C& "", ""&R[3]C& "", "" &R[4]C& "", ""&R[5]C& "", "" &R[6]C& "", ""&R[7]C& "", "" &R[8]C& "", ""&R[9]C& "", "" &R[10]C"
Rows(102 & ":" & 111).EntireRow.Hidden = True
End Sub

Edit*** Added the last line of code to hide rows 102-111

 

[Desu Nota from Columbus]

Did this macro accomplish what you wanted? It worked with my test data.