Converting Two Ranges to Arrays and Replacing Values

rexibit

New Member
Joined
Mar 7, 2011
Messages
3
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:
Code:
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.
 

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.

Tinus Bergakker

Board Regular
Joined
Jan 6, 2011
Messages
153
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

New Member
Joined
Mar 7, 2011
Messages
3
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:

Code:
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

MrExcel MVP
Joined
Sep 2, 2009
Messages
16,434
Code:
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

Board Regular
Joined
Jan 6, 2011
Messages
153
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

New Member
Joined
Mar 7, 2011
Messages
3
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. :)
 

Forum statistics

Threads
1,141,018
Messages
5,703,754
Members
421,313
Latest member
Mooncake1

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top