VBA- Find location of first cell in named range to reference heading row of spreadsheet

Question

What I have is part descriptions that I have split into separate columns. All columns have headings, but not all columns contain information for every part, ex: some might have size, material, and temp while another may have just size and temp. I have a function to concatenate them which ignores the blank spaces. I want to reference the header for the column before each cell I am concatenating.

Desired results:

When entering the following in B6 =ConcatenateRangeValve(G6:J6,",")

I want to see these results. [ITEM]Valve,[TYPE]Gate,[DIM]28IN

The items in [ ] are in Row 1:1 and I am having trouble getting my function to reference that row with the same column that I am in to pull the header. I think what it needs is to identify where the cell I am working in is in the whole spreadsheet. I attempted to do this by defining C, setting its value as the column number of the first cell in my range and then increasing it by 1 as it steps though the loop. I cannot get it to work. All the other pieces are fine. See below:

    Function ConcatenateRangeValve(ByVal cell_range As Range, _
         Optional ByVal seperator As String) As String



    Dim newString As String
    Dim cellArray As Variant
    Dim i As Long, j As Long
    Dim C As Long


    cellArray = cell_range.Value

    With Range("cell_range")
    C = .Column
    End With

    For i = 1 To UBound(cellArray, 1)
        For j = 1 To UBound(cellArray, 2)
              If Len(cellArray(i, j)) <> 0 Then
                newString = newString & (seperator & "[" & cells(1, C) & "]")
                newString = newString & (cellArray(i, j))
            End If
           C = C + 1
        Next
    Next

    If Len(newString) <> 0 Then
        newString = Right$(newString, (Len(newString) - Len(seperator)))
    End If

    ConcatenateRangeValve = newString

    End Function

Thanks in advance for any help you guys can offer.

Answer 1

You can do it many different ways, but you can reference cells inside a range the same way as with a sheet. If your named range conatins the headers:

Range("NamedRange").Cells(1,1)

If your named range starts just below the headers:

Range("NamedRange").Offset(-1,0)

For all other cases:

Range(Cells(1,Range("NamedRange").Column))