Replace with an ARRAY

[Titian]

I am trying to speed up execution of a macro by use of an array. I have read into

myarray(i, 1)

a list of product codes that I want to tag if they appear in a larger dataset.

My original method of matching the small list against the large list was the following code copied down on each row of the large list then autofiltered on FALSE

ActiveCell.FormulaR1C1 = _
        "=ISNA(MATCH(TRIM(RC[-17]),[Index.xlsm]Work1!R1C1:R" & Keeplr & "C1,0))"

Presuming that use of an array will be quicker, I would like to replace the - [Index.xlsm]Work1!R1C1:R" & Keeplr & "C1, - part of that code line with myarray(i,1),
how do I do that?

Any suggestions appreciated.

 

[GlennUK]

Show more of your code. How many cells are you putting the formula into? Why don't you assign the formula in one go instead of copying?
Like:

Range("Z1:Z999").FormulaR1C1 = _
        "=ISNA(MATCH(TRIM(RC[-17]),[Index.xlsm]Work1!R1C1:R" & Keeplr & "C1,0))"

What speed difference does that make alone?

In fact, selecting cells and objects instead of operating on them directly is not recommended.

 

[Titian]

Thanks Glenn.

My test data targets are 117 products in the "small" file

My "large" extract file is 34,000 rows long

There are 203 matches found (not all 117 products are present and a number of target products appear a number of times)

Using those file sizes there is little if any difference to processing speeds - however I appreciate your suggestion regarding assigning the formula directly which I have adopted.

The only other processing done by the very simple macro is the Autofilter.

I have no experience of using arrays in this way that's why I posted the question although I am quite happy to use your code improvement.

Thanks for your interest.

 

[GlennUK]

If you are putting that formula into 34000 cells in a loop, then I hope you turning calculation to manual before you start, and then turnining it back to automatic afterwards. Also, setting Application.Screenupdating to false can help with speed.

As for using an array ... you could calculate the match against your array in code and post the result directly into the cell, bypassing cell formulas. Is that the kind of thing you were thinking of? It would mean looping throught the 34000 cells.

Here's a little example to show you what I mean:

    myarray = Range("Check_List").Value
    For Each c In Range("E1:E10")
        varres = Application.Match(c, myarray, 0)
        If IsError(varres) Then
            c.Offset(0, 1).Value = "Not found"
        Else
            c.Offset(0, 1).Value = "OK!"
        End If
    Next

 

[Titian]

I have set Application.Screenupdating to false as a matter of routine. The time it takes to write the formula to a more representative dataset of 128,000 rows is literally the blink of an eye - the delay is in the Autofilter when Excel reports that the program is not responding until a little while later it produces the result.

With Sheets("socx25")
        .Range(rng).Autofilter Field:=21, Criteria1:=True
    End With
    
    Set TARGETRNG = Sheets("socx25").Range(rng)
        With TARGETRNG
            On Error Resume Next
            .SpecialCells(xlCellTypeVisible).EntireRow.Delete
    End With

Is what I am doing here correct as I guess your suggested code would need to be subject to a similar process.

Thanks for the suggestion.

 

[GlennUK]

If you know the Autofilter is the processing bottleneck, have you experimented with different approaches? Firstly, try doing Advanced Filter to another location, to put your FALSES into another sheet, to get the subset that you want ... I find that is much faster that using Autofilter and deleting.

 

[Titian]

Great suggestion - it flies now!

I'm just a light weight user as you have seen. I dib in from time if I think I can save a few minutes here and there. The advice from you guys is invaluable.

Many thanks.

 

[GlennUK]

That's great! I'm so glad the improvement was so good.

My pleasure to help