Replies

If SERV is a variable,

With WW.Columns(“O:O”)
Set Found_Index = .Cells(Application.Match(SERV & “*”, .Cells, 0), 1)
End With

If “SERV” is the string you want to match,

With WW.Columns(“O:O”)
Set Found_Index = .Cells(Application.Match(“SERV*”, .Cells, 0), 1)
End With

=VLOOKUP(A1,{“None”,0;”Heat”,1;”Melt”,2;”Growth”,3;”Anneal”,4;”Cool”,5;”E_cool”,6},2,FALSE)

This works for me
Userform1.Label1.BackColor = Range(“A1”).Interior.Color

The first array in LOOKUP has to be sorted ascending. Try

=LOOKUP(A1,{“Anneal”,”Cool”,”E_cool”,”Heat”,”Melt”,”None”},{3,1,0,4,5,6})

The unqualified Cells in the line
Set oscell = Cells.Find(k, , , , , xlNextTrue)
references the Active Sheet. Adding a dot
Set oscell = . Cells.Find(k, , , , , xlNextTrue)

will cause it to reference the sheet specified in the With statement

If you have ModelA etc in column A and 2;0;1 in column B,
Selecting three cells in a column and entering the array formula

=INDEX(A:A,SMALL(IF(B1:B30,ROW(B1:B3),999),ROW(Z1:Z3)),1)&””

should do what you want.
The B1:B3 can be changed to start in some row other than 1, but the Z1:Z3 range must start in row 1, its purpose is to generate the array {1;2;3…}

Array formulas need to be confirmed with Ctrl-Shift-Enter (Cmd+Return for Mac)

VBA is not nessesary
=SUBSTITUTE(MID(A6,FIND(CHAR(5),SUBSTITUTE(A6&””,””,CHAR(5),MAX(1,LEN(A6)-LEN(SUBSTITUTE(A6,””,””))-1))),255),””,””)

You might make an Excel>Options>Custom List of the headers in the order that you want.
Then sort (left to right) on that custom list.

Sub test()

With ThisWorkbook.Sheets("Sheet1"): Rem adjust

    With Range(.Cells(2, 1), .Cells(.Rows.Count, 12).End(xlUp))
    
        Rem sort by column L
        .Sort Key1:=.Columns(12), Order1:=xlAscending, Header:=xlGuess, _
            OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
        
        Rem cut/insert blank cell rows to the top
        Application.CutCopyMode = False
        On Error GoTo HaltRoutine
        Application.Intersect(.EntireColumn, _
                    .Columns(12).SpecialCells(xlCellTypeBlanks).EntireRow).Cut
        .Range("a1").Insert shift:=xlDown

HaltRoutine:
        On Error GoTo 0
        Application.CutCopyMode = False
    End With
End With

End Sub

I keep my generally useful macros in my Personal Macro Workbook.
To distribute them, I’d send a workbook with them in it to my co-workers and let them choose the macros that they find useful.
An add-in would have to be distributed like a workbook. (i.e. e-mail a file to someone)

Handling problem situations and how to do updates would depend on how critical your macros are to the job at hand and what resources (including your time) the company is willing to allocate.

You can use the WorksheetFunction Index to return a specific row or column of a 2-Dimensional array.

    Rem returns third column as a 2-D (n rows X 1 column) array
myColumnA = Application.Index(myArray, 0, 3)

    Rem returns third column as a 1-D array
myColumnB = Application.Transpose(Application.Index(myArray, 0, 3))

    Rem returns the second row as a 1-D array
myRow = Application.Index(myArray, 2, 0)

This fails with larger arrays.
On my Mac it fails if myArray has more than 4095 (=2^12 – 1) elements. It returns a TypeMismatch error.
The Windows versions have a much larger limit on WorksheetFunction.Index.

Your 5X7 is well within either limit.

(Mac OS10.5 Excel2004)

Dim myArray as Variant
myArray = Array(ArrCrew1Days, ArrCrew2Days,...)

is one way to do what you describe.

Using a 2-dimemsional array is another.

Dim myArray as Variant
myArray = Range("A1:H10").Value

If you use the 2D array approach, the WorksheetFunction. Index can be used to extract whole rows or colums from myArray.

SUMIF needs three arguements.
Perhaps =SUMIF(A1:A6, 2, A1:A6) will work for you.

(The =COUNTIF(A1:A6,2)*2 is possibly faster)

Combining MAX with the logic of the IF would give

=MAX(1, ROUNDUP((F5*F1)+F5,1)-0.01)

UpDownToNorma is missing an L. It restores the up/down arrows to their normal functioning

That code would go in a normal code module, not a sheet’s code module. You would call AssignUp_DownKeys whenever you wanted the arrows to effect the listbox selection and UpDownToNorma when you want them to have their normal function.