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.

Fornax

Answer 1

So, I am new to VBA, apparently I was using named range wrong. The solution I came up with was to replace

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

With

' cell_range(1).column returns the column index of the starting range from the passed variable
  C = cell_range(1).Column

This returned the number corresponding to the column in the spreadsheet were the first cell of the array was located. This allowed me to reference the column heading using C.

Fornax

Answer 2

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))

Griff

Answer 3

I'm not 100% this relates to OP's question, but as far as title and future readers are concerned, this worked for me.

Dim Rng As Range, FirstIteminRng As Range
Set Rng = Range("A1:B10")
Set FirstIteminRng = Cells(Rng.Row,Rng.Column)
FirstIteminRng.Select

Lees