need to modify user defined function macro

CSTUBBS

New Member
Joined
Feb 13, 2007
Messages
45
The below macro(found in VBA and MACROS for Excel) works well but I need a few modifications
Code:
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
 
So basically we're losing the letters and parentheses?

But if there are 2 numbers seperated by parentheses we keep the parentheses?

PS Sorry for all the questions, just want some clarification before I start on anything.:)
 
Upvote 0

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.
That was 1 track of a 2 track kiln. It is pulling data for each cell through vlookup formulas, which are coming from the database. There is a data entry sheet that the operators are llowed to enter data on, which is transfered to the database when completed correctly.

Several lines of the database will be sent soon
 
Upvote 0
test kiln data program.xls
ATAUAVAWAXAYAZBABBBCBDBEBFBGBHBI
2COLUMN#1COLUMN#2
3111DATE112DATE113DATE114DATE121DATE122DATE123DATE124DATE
4
5                
6
7
884/1764/1964/2064/16(6/8)4/19(6/6)4/20
9104/20104/20104/20104/20124/20124/20124/20124/19
1044/2044/1844/944/1944/1944/1944/1844/17
1184/1984/1984/1984/1984/1884/19
12104/20104/20104/19104/19104/20104/19104/19104/20
DATABASE
 
Upvote 0
Small sample of the database that info is coming from. The info for this function is from AT8 to FQ8. Only AT8 to BI8 is shown.

From the historical(the first upload) you see the (HG10),(HG12),(HG6), and 12 entries. That was only the first track. The second track (which I didn't send) is almost identical, so the concatenated output would be:
(HG6),(HG10),(HG12),12

If there had been (2) 8 footers on top of a 16 footer, the output would be:

(HG6),(HG10),(HG12),12,(6/8)

I hope this clarifies things. I work with this "STUFF" all day long, and i take for granted that others have no idea what i'm trying to do.

Again, thanks for the help and interest
 
Upvote 0
Sample data is good but could you please answer my previous questions?:)

PS The data you've posted doesn't seem to correspond with your other posts.:)
 
Upvote 0
Code:
So basically we're losing the letters and parentheses? 

But if there are 2 numbers seperated by parentheses we keep the parentheses? 

PS Sorry for all the questions, just want some clarification before I start on anything.


We do still use the letters and parenthesis. The (HG10) represents 10 inch wide high grade lumber. The (4/6) represents 4 inch and 6 inch wide lumber--these can only be on top of a 16 foot length.
From the visible part of line 8 on the "database" sheet, the output needs to be:
8,6,(6/8),(6/6)

From the visible part of line 9 on the "database" sheet, the output needs to be:

10,12

From the visible part of line 10 on the "database" sheet, the output needs to be:

4

From the visible part of line 11 on the "database" sheet, the output needs to be:

8

And from the visible part of line 12 on the "database" sheet, the output needs to be:

10


I hope that clarified things. Let me know if you need more info
 
Upvote 0

Forum statistics

Threads
1,223,228
Messages
6,170,871
Members
452,363
Latest member
merico17

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
Back
Top