Posts by pulsar777


    a syntax that makes Userform controls looks something like this :

    I'd like to add Click event to a commandbutton.
    Any suggestions ?

    I'd like to list all available Libraries in first column and each level of their respective class and property to next columns,
    to compare Office 2010 with 2016 References.

    I've managed to list only active references, using elements from MS Visual Basic Extensibility 5.3 library :

    Thank you

    Re: Conditional formatting _ array of string criteria

    Hi Ger Plante,

    thanks. Though, Array often changes and might have even 1000 strings.
    Not sure how many separate rules might I make by your code.

    Probably to copy Array to some range and change type from



    and apply by a MATCH function?

    Or might be there a better way?

    Hi all,

    I'd like to apply 1 formatting rule,
    with multiple text conditions.

    Something like this:

    With Range("A:A")
        .FormatConditions.Add Type:=xlTextString, String:=Array("01234", "56789"), TextOperator:=xlContains
        .FormatConditions(1).Interior.Color = vbYellow
    End With

    I'd like to avoid:
    a) looping Arrays and Cells on worksheet to apply formatting individually based on If statement (too much looping ..)
    b) creating =OR() function in conditional formatting setup, while array would probably exceed that functions arguments limit.

    Thank you for any advice!

    Re: Get unique items from large array fast

    Hi, thank you both, this is really useful.

    At 2 columns of 100K of data,
    Collections took 5 secs while Dictionaries only 3.1 secs.
    Both are case insensitive.
    Is there a way to apply Text compare rather than binary? But still, I can overcome it with UPPER worksheet function just in case.

    KjBox: the Application.Match failed with type mismatch, cause it probably expected range as 2nd argument (rather than y).

    Re: Get unique items from large array fast

    KjBox, thanks again.

    I think I'll stick with the Collections.
    Dictionaries seem to be way slower over such large arrays.

    I also found strange that when I had 100K rows rather than 50K,
    application could transpose only somewhat over 34K items,
    unlike all 50K in original example.

    But anyway, your help was very descriptive and appreciated.
    Keep up the same good approach !

    Hi everyone,

    I'd have 2 large ranges:

    Both contain duplicates.
    I'd like to determine which unique values from A range are contained in B range.

    I thought of ruling out these methods:
    a) Looping thru arrays and making array of unique values
    b) Range.AdvancedFilter Unique:=True
    c) Creating pivot tables

    I found on Ozgrid this nice looking method:

    Dim V As Variant
        With Range("A1:A50000")
            V = Filter(Evaluate("TRANSPOSE(IF(COUNTIF(OFFSET(" & _
                .Address & ",,,ROW(1:" & .Rows.Count & "))," & _
                .Address & ")=1," & .Address & "))"), False, 0)
        End With

    But it still takes long processing time.

    What would you suggest as fastest way ?

    in attached file I made example of a Range method to find by country a combination of 2 values that add up to amount on the right.
    However, Belgium doesn't get its Cell Addresses, because 138 is Sum of 3 values (E9, E10, E16).

    I look for a looping mechanism, that would go through each 2-combination of values until it finds the result,
    otherwise would continue with 3-combination, 4 .. up to all items combination necessary to find respective SUM.

    A-B, A-C, A-D ..... A-Z
    A-B-C, A-B-D ...A-B-Z ; A-C-D .. A-C-Z ...

    I thought of using an Array, but don't know how.


    Re: Find out if formula is absolute

    Hi Luke,

    yes, basically I used same method - Application.ConvertFormula

    Let's say some cells of the range contain formula like this:


    Then those cells after running the macro change result to #VALUE error.