Missed to declare the variable. Try the below

Function Mstring(myRange As Range, myHeader As Range) As String
Dim cell As Range
For Each cell In myRange.Cells
If UCase(cell.Text) = "Y" Then
If cell.Column <= 4 Then
Mstring = "Tool"
Else
Mstring = Mstring & " , " & myHeader(cell.Column)
End If
End If
Next
End Function


If this post helps click Yes
---------------
Jacob Skaria


"Jen_T" wrote:

Jacob,

Thank you for the code. I am receiving a "Variable not defined error" on
"cell" ?

"Jacob Skaria" wrote:

Try this UDF (User Defined function). From workbook launch VBE using Alt+F11.
From menu Insert a Module and paste the below function.Close and get back to
workbook and try the below formula.

In cell G2 you can use the formula
=mstring(dataRange,headerRange)
=mstring(A2:F2,$A$1:$F$1)

Function Mstring(myRange As Range, myHeader As Range) As String
For Each cell In myRange.Cells
If UCase(cell.Text) = "Y" Then
If cell.Column <= 4 Then
Mstring = "Tool"
Else
Mstring = Mstring & " , " & myHeader(cell.Column)
End If
End If
Next
End Function

If this post helps click Yes
---------------
Jacob Skaria


"Jen_T" wrote:

Is there a function I could create in Excel 2007 VBA module that would look
a range of cells and pull in the values.
In columns A - F, I have column headers,
Column A - Hand Tools
Column B - Power Tools
Column C - Lawn and Garden Tools
Column D - Misc Tools
Column E - Yard Accessories
Column F - Misc
Each will have values of either a "Y" or"N", starting in row 2
In Column G, header is titled "Equip. Sold".
I have a macro that pulls in the column header if ="Y" in columns A - F.
However I am looking to achieve something a little different. I need to
indicate "Tools" in cell G2 to final row, if cell value = "Y" in any of the
columns A through D, than pull in remaining columns headers, E through F if
cell value ="Y".

Example:
If column A(Hand Tools) ="Y"
column B (Power Tools) ="N'
column C (Lawn and Garden Tools)="Y"
column D (Misc Tools) ="Y"
column E ( Yard Accessories) ="N"
column F ( Misc) ="Y".
Than cell G2 -= Tool, Yard Accessories, Misc

Here is my current module I am using:

Function Mstring(myrange)
Set myEquip = Range("A1:F1")
mycount = myrange.Count
myflag = True
For j = 1 To mycount
If myrange(j) = "Y" Then
If myflag Then
Mstring = Mstring & myEquip(j)
myflag = False
Else
Mstring = Mstring & "," & myEquip(j)
End If
End If
Next j
End Function

Thank you for your suggestions in advance. I would include an attachment but
not sure if possible in this discussion group, if so please advise how to
include attachments. Thank you :)