need to modify user defined function macro

[CSTUBBS]

The below macro(found in VBA and MACROS for Excel) works well but I need a few modifications

Option Explicit

Function SortConcat(Rng As Range) As Variant
    'Rng —The range of data to be sorted and concatenated.
    Dim MySum As String, arr1() As String
    Dim j As Integer, i As Integer
    Dim cl As Range
    Dim concat As Variant
    On Error GoTo FuncFail:
    'initialize output
    SortConcat = 0#
    'avoid user issues
    If Rng.Count = 0 Then Exit Function
    'get range into variant variable holding array
    ReDim arr1(1 To Rng.Count)
    'fill array
    i = 1
    For Each cl In Rng
        arr1(i) = cl.Value
        i = i + 1
    Next
    'sort array elements
    Call BubbleSort(arr1)
    'create string from array elements
    For j = UBound(arr1) To 1 Step -1
        If Not IsEmpty(arr1(j)) Then
            MySum = arr1(j) & "," & MySum
        End If
    Next j
    'assign value to function
    SortConcat = Left(MySum, Len(MySum) - 2)
    'exit point
concat_exit:
    Exit Function
    'display error in cell
FuncFail:
    SortConcat = Err.Number & "-" & Err.Description
    Resume concat_exit
End Function

Sub BubbleSort(List() As String)
' Sorts the List array in ascending order
    Dim First As Integer, Last As Integer
    Dim i As Integer, j As Integer
    Dim Temp
    
    First = LBound(List)
    Last = UBound(List)
    For i = First To Last - 1
        For j = i + 1 To Last
            If UCase(List(i)) > UCase(List(j)) Then
                Temp = List(j)
                List(j) = List(i)
                List(i) = Temp
            End If
        Next j
    Next i
End Sub

1)My array is 128 cells wide(AT3:FQ3) and i get a type mismatch--seems to work well for 36 cells

2) These cells contain dates and numbes---all i want to sort and/or display is the numbers
Note: some numbers are in parenthesis and other are either single or double digits. Example: numbers are (8/8),8,10,(HG6),4,6,etc. And Dates are 04/06 format (no year, and no parenthesis)

3) All empty cells in the array show up in the above SortConcat function as zero, and are un-necessary

4) The final output should be no more than 6 concatenated numbers, so column width is not an issue, and the bubble sort is really not necessary but would be a nice feature

Sounds easy,huh?
Any help would be much appreciated

 

[CSTUBBS]

Still need the help, and it will be much appreciated

 

[Norie]

CSTUBBS

Could you actually tell us what the function does?

 

[CSTUBBS]

The function is used to search through 128 vertical cells of information. Cell AT3 will have a lumber width, and AU3 will have a date of manufacture. These alternate through the whole range giving width/date/width/date etc for 64 different units(loaded in a dry kiln).

I need the function to write into a specified cell(where the function is located, and in the same row as the data) whether the kiln is all one width lumber, or a combination of widths (ex 4,6 or 4 or 6 or 8,10,12).

The current function gives all units(4,4,4,4,4,8,12,12,12), and all dates (05/07/2007,05/07/2007,05/05/2007, etc) which are sorted from lowest to highest in the same cell as the function is(128 different numbers, many of which are repeating) All I need it to do is list the widths that are in the kiln (4,8,12).

If more explanation is needed, feel free to ask

 

[Norie]

So basically you just want to get the unique values in the row concatenated and seperated by commas in one cell?

And you just want the numbers from these values?

 

[CSTUBBS]

Correct. Only the unique numbers, and no dates. There is one twist though---some of the numbers can be in parethesis with letters or / . All posible demensions are listed below:
4,6,8,10,12,(HG6),(HG8),(HG10),(HG12),(4/4),(4/6),(4/8),(4/10),(4/12),(6/4),(6/6),(6/8),(6/10),(6/12),(8/4),(8/6),(8/8),(8/10),(8/12),(10/4),(10/6),(10/8),(10/10),(10/12),(12/4),(12/6),(12/8),(12/10),(12/12),(HG6HP),(HG8HP),(HG10HP),(HG12HP)

Your help is much appreciated.

 

[Norie]

CSTUBBS

I would suggest you basically start over.

ie a brand new UDF rather than adapting this existing one.

Mind you we could probably plunder some of the code.

Some sample data would be useful to 'play' with and also how would the
values like (4/8) be dealt with.

Is that 2 seperate dimensions, or do we take one over the other?

I'd post code but don't really have time at the moment.

Here's a small start though, it concatenates all the values in a range of one row.

Function ConCatNums(rng As Range) As String
Dim arrVals
    
    If rng.Rows.Count <> 1 Then
        ConCatNums = "Only 1 row possible."
        Exit Function
    End If
    
    arrVals = rng.Value
    
    arrVals = Application.Transpose(arrVals)
    
    arrVals = Application.Transpose(arrVals)
    
    ConCatNums = Join(arrVals, ",")
    
End Function

I'll try and post back more later.

 

[CSTUBBS]

Thanks Norie. Sorry about the long delay,but in meetings most of the day.
Any help would be appreciated.

 

[Norie]

CSTUBBS

Could you clear up the issue of the values like (4/6)?

I don't want to start posting code that doesn't deal correctly with those.

 

[CSTUBBS]

The (4/6) is only allowed if the pack length is 16 feet long. That is already set up in the data input sheet. What it tells the operators is that there are (2) 8 ft packs sitting end to end totalling 16 ft length, and has 16 footers either above or below it
I would like the function to tell me if these combined packs are there. The numbers in the array would be:
AT3=4
AU3=05/01/2007
AV3=4
AW3=05/03/2007
AX3=(4/6)
AY3=05/04/2007
AZ3=(6/4)
BA3=04/30/2007
BB3=10
BC3=05/07/2007
ETC,ETC,ETC

The output of the function would be in cell C3 and would be
4,(4/6),(6/4),10 etc.
There are specs that the operators use to set up kilns, so the total number of different widths in one kiln should rarely be above 6 individula numbers
Notice the 4 is only displayed once, and the (4/6), and (6/4) are displayed separately.

Tell me if you need more, and again, Thanks