Converting Two Ranges to Arrays and Replacing Values

[rexibit]

I am trying to take two ranges (one for column headings of a section and the other contains the values within those columns), replace any values in the column of Range 2 with the values in the column of Range 1.

Also, the output will be concatenated together with a delimiter of |. The two ranges should only have one row and both have the same number of columns.

I wrote a custom Excel function that I think should work, but it keeps giving me this error: "A value used in the formula is of the wrong data type". I keep looking over it and it all looks like I have the correct data types referencing one another.

Sample Values:
Function: ConcatenateRangeReplace(HeadingRange, ColumnValueRange, "|")
Headings: Heading 1, Heading 2, Heading 3, Heading 4, Heading 5
Column Values: Null, Null, x, Null, x
Desired Output: Heading 3|Heading 5

Code I have so far:

Function ConcatenateRangeReplace(Headings As Range, Parts As Range, Separator As String)
     ' Build a single string from a passed range with a
     ' passed separator between each value
     ' Replace values of range items with heading values in range
     ' (i.e. change an "x" to "Classification") - useful for bulk uploads to Expression Engine CMS Installations
    Dim tempString, tempSeparator As String
    Dim partsArray, headingsArray As Variant
    Dim i As Long
    
    tempString = ""
    partsArray = Range(Parts).Value
    headingsArray = Range(Headings).Value
    
    ' Check that both ranges have the same amount of rows and columns
    If Len(partsArray) = Len(headingsArray) Then
         ' Check each part of the array for a value
        For i = 1 To UBound(partsArray)
            ' Make sure no spaces pass as a null value
              If partsArray(1, i) = " " Then
                partsArray(1, i) = ""
              End If
            ' Check if part is empty
            If partsArray(1, i) = "" Or partsArray(1, i) = 0 Then
                tempSeparator = ""
            Else
                tempSeparator = Separator
            End If
            If Len(tempString) = 0 Then
                ' Replace part with heading if part is not empty
                If Not partsArray(1, i) = "" Then
                    tempString = headingsArray(1, i)
                Else
                    tempString = partsArray(1, i)
                End If
            Else
                ' Replace part with heading if part is not empty
                If Not partsArray(1, i) = "" Then
                    tempString = tempString & tempSeparator & headingsArray(1, i)
                Else
                    tempString = tempString & tempSeparator & partsArray(1, i)
                End If
            End If
        Next
        ConcatenateRangeReplace = tempString
    Else
        MsgBox ("Both ranges must have only one row & the same number of columns")
    End If
End Function

Any insight into this would be greatly appreciated.

 

[Tinus Bergakker]

The only thing I can see wrong is the use of the Len function.

When working with array sizes usually you keep to the Ubound check.

Also
partsArray = Range(Parts).Value
headingsArray = Range(Headings).Value

Headings and Parts are already defined as ranges. Rather use
partsArray = Parts.Value
headingsArray = Headings.Value

 

[rexibit]

Thanks Tinus. I only put in that IF statement with the Len as an after thought when the function was already not working, thinking it'd give some notification. I changed it to:

If UBound(partsArray) = UBound(headingsArray) Then

Also, I changed the Range declaration to what you suggested. I was looking at other tutorials on how to convert a range to an array and only saw static declarations of "A1:D9" and thought that's where I put in the name.

After making the range declaration change it stops saying #VALUE in the cells and now says 0, so that's some progress. It's still not doing what I need it to do.

 

[AlphaFrog]

Function ConcatenateRangeReplace(Headings As Range, Parts As Range, Separator As String) As String
     ' Build a single string from a passed range with a
     ' passed separator between each value
     ' Replace values of range items with heading values in range
     ' (i.e. change an "x" to "Classification") - useful for bulk uploads to Expression Engine CMS Installations
    Dim tempString As String
    Dim i As Long
    
    ' Check that both ranges have the same amount of rows and columns
    If Headings.Columns.Count = Parts.Columns.Count And _
       Headings.Rows.Count = Parts.Rows.Count And _
       Headings.Rows.Count = 1 Then
       
        ' Concatenate headings were thethere part = X
        For i = 1 To Headings.Cells.Count
            If UCase(Trim(Parts(i))) = "X" Then tempString = tempString & Headings(i) & Separator
        Next i
        
        ' Remove trailing Separator
        If tempString <> vbNullString Then
            tempString = Left(tempString, Len(tempString) - Len(Separator))
            ConcatenateRangeReplace = tempString
        End If
        
    End If
    
End Function

 

[Tinus Bergakker]

I ran your function and the problem is that the Ubound is picking up the whole range as being 1 entry.

You need to refer to the second dimension (the columns) as your upperbound,

e.g.
UBound(partsArray,2)

 

[rexibit]

Thanks Tim and Alpha. Both of those work, I tested both and they seem to work the same. I guess Alpha's would perform faster than the one I wrote since there's less processing overhead.

I spent several hours yesterday looking for how to reference a specific element in a range, but nothing I tried worked and decided to go with converting it to an array. Alpha answered my question on how to do that.

Thanks, ya'll have been a bunch of help.