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