Posts by ujambor


    Have little experience with usage of public/global subs with MSforms on userforms (UF). I need to declare a public sub over MSform object (eg. textbox). I have 30 command buttons. Each one has a routine, that does something with corresponding textboxes/comboboxes/checkboxes/radiobuttons based on a value from worksheet (YES/NO). I have the code personalized over each object working already without public subs, but is miles long and maintenance is extremly slow at any change.

    I would like to shrink the code by declaring public sub to generic MSform object on UF. Now, what I am trying to do is that by clicking on command button, it would call TB101color, this routine declares specific object, checkes if the corresponding value is YES or NO. Based on YES or NO value, routine calls another public routine TB1a or TB1b, both of them are set on generic public object. TB1a is a generic routine, that is suitable for all other similar MSform objects. I hope I am going in the right direction.

    Also question is, where do i place this code, on userform or on workbook object?

    thanks for any hints!

    Re: Find/extract "largest string" based on numbers in multiple text strings

    For some unexplained reason this formula works until some 270 row, afterwards no more. Since it was probably an issue with cell referencing, I displaced referencing the cell as "A6" with "OFFSET(A1;COUNTA(A1:A6)-1). This did the trick.

    Final formula is:
    =MAX(LEFT(A1:OFFSET(A1;COUNTA(A1:A6)-1) ,FIND("-",A1:OFFSET(A1;COUNTA(A1:A6)-1)&"-")-1)+0)&"-"&MAX(IF(LEFT(A1:OFFSET(A1;COUNTA(A1:A6)-1),FIND("-",A1:OFFSET(A1;COUNTA(A1:A6)-1)&"-")-1)+0=MAX(LEFT(A1:OFFSET(A1;COUNTA(A1:A6)-1),FIND("-",A1:OFFSET(A1;COUNTA(A1:A6)-1)&"-")-1)+0),MID(A1:OFFSET(A1;COUNTA(A1:A6)-1) ,FIND("-",A1:OFFSET(A1;COUNTA(A1:A6)-1) &"-")+1,255)+0))+1

    Still, I have one question: what does the number 255 in formula stand for? Thanks!!!

    In range "A1:A6" is text


    First two digits are year abreviation (10,11,12) and dash separates certain round serial number (3,4,22,120,456,1920).

    I need a formula (in one cell) that displays the value - "largest string", which is based on two conditions:

    • (1st) formula searches for largest year abreviation (dominant condition) and
    • (2nd) formula searches for largest serial number (after dash and based on largest year abreviation found).

    With regard on written, correct result would be "12-22".

    Second formula should also include that serial number in "largest string" found is added by +1 (so the value shown is serial No. of next/new proposal).
    With regard on written, correct result would be "12-22(+1)" or "12-23".

    Predominantly two formulas are wanted, macro is also all right.

    Thank you!

    Re: count & check cells in a range for exact number of digits

    Real stuff has over 30.000 cells in multiple columns. Conditional formatting is too slow. Also I cant use conditional data validation, since user usualy copies ranges by himself or does that with macro - both actions override conditional validation.

    In range A1:A10 there are different values in cells, some only digits, some digits with characters. Only entries with 10 digits are correct ones! All digits 0123456789 apply.

    I need a VBA code that will check each cell in range for exactly 10 digits. If there are entries that do not match this condition, msgbox should appear -saying:
    - either in which rows there are wrong values
    - either exactly in which cells there are wrong entries or
    - preferable Excel directly selects (without any msgbox) the first cell with wrong input, so that user can quickly correct it. After user changes content of a cell and hits ENTER key, macro runs again from the start checking next wrong entry. This sounds as combination of sub and private sub routine.

    Any help greatly apreciated!

    Re: Generate a list of unique values

    Try this one:

    [/B]Function UNIQUE(InputRange As Range, ItemNo As Long) As VariantDim cl As Range, cUnique As New Collection, cValue As Variant    Application.Volatile    On Error Resume Next    For Each cl In InputRange        If cl.Formula <> "" Then            cUnique.Add cl.Value, CStr(cl.Value)        End If    Next cl    UNIQUE= ""    If ItemNo = 0 Then        UNIQUE= cUnique.Count    Else        If ItemNo <= cUnique.Count Then            UNIQUE= cUnique(ItemNo)        End If    End If    On Error GoTo 0End Function[B]


    Apply formula as:
    =UNIQUE("place here a range of cells";"place here a number")
    number 1,2,3.... number tells you the next different unique value.
    When no different values are found, next cell in a row beneath is blank.

    hope this helps anyone...