How to read the column header

[nemesis52]

Hi,

I have a newbie in Excel VBA, so sorry if I ask easy question.
My problem is :

I have a table in Excel 2007. The header of columns is in row 3.
The value of each cell could be "+" or "-".

I need to make a function to make a string to fill the last cell of each row and this function supposes to check each cell in range and if it is "+" add the column header to the output string.

Example

Header : H1 | H2 | H3 | H4 | Output Function
row 1 : + | + | + | - | H1,H2,H3
row 2 : - | - | + | + | H3,H4
.
.
.

I need this function to take range as an input and then find the absolute reference of each column header. This is my problem that I can not find a way how to connect the relative position of each cell in input range to the absolute reference of header cell.

I know it might be a bit confusing but I tried to clear myself.


Thanks

 

[chergh]

Public Function OutputStr(IRange As Range) As String


Dim rng As Range
Dim strResult As String

If IRange.Rows.Count > 1 Then

    MsgBox "select one row only"

    Exit Function

End If


For Each rng In IRange

   If rng.Value = "+" Then
      
      strResult = strResult & rng.End(xlUp).Value
   
   End If

Next rng

If strResult <> "" Then strRresult = Left(strResult, Len(strResult) - 1)

OutputStr = strResult

End Function