Can I use UBound for delimited cells?

[USAMax]

I can do this but it would be long than it should be.

Each cell is semi-colon delimited and I could change it to a comma or anything. The problem is that the child must have all the same properties as the parent plus the values of the child and I thought a UBound would work. like this:

Code:
For x = 0 To UBound(Cells(CurRow, OptCol))
IsMatch = False
For y = 0 To UBound(Cells(TmpRow, OptCol))
If x = y Then IsMatch = True
Next y
Next x

Here is sample data:


Samples:
Parent: MOTOPATCH; CD
Child: MOTOPATCH
Desired Result for Child: MOTOPATCH; CD

Parent: ATR
Child: SSC; SA
Desired Result for Child: ATR; SSC; SA

Parent: EEPROM; MOTOPATCH; CD
Child: MOTOPATCH
Desired Result for Child: EEPROM; MOTOPATCH; CD


Thank you,

 

[Scott Huish]

Your code is returning Boolean values, but your sample looks like you are trying to return all the unique items from Parent and Child. What are you trying to do exactly? I'm guessing that the Parent and Child text is not part of the value in the cell but the column heading?

 

[USAMax]

Hello Scott:

In the sample data the Parent and Child are both cells on a form. The Desired Results is how the Child cell should look after it is complete. I do not have much experience with UBound but it is what I thought I should use.

 

[Scott Huish]

I think this may be what you want:

Function CombineUnique(r As Range) As String
Dim b, c, d, s As String
With CreateObject("scripting.dictionary")
    For Each c In r
        d = Split(c, ";")
            For Each b In d
                b = Trim(b)
                If Not .exists(b) Then
                    .Add b, 1
                    s = s & b & "; "
                End If
             Next
    Next
End With
If Len(s) Then CombineUnique = Left(s, Len(s) - 2)
End Function

Excel Workbook
	A	B	C
1	MOTOPATCH; CD	MOTOPATCH	MOTOPATCH; CD
2	ATR	SSC; SA	ATR; SSC; SA
3	EEPROM; MOTOPATCH; CD	MOTOPATCH	EEPROM; MOTOPATCH; CD
Sheet1

 

[USAMax]

Unbelievable job Scott, I had it almost working then I broke them up into an array with half the lines and you have half the lines I ended up with. I have never used Split but it is in my repertoire now!

Thank you,