How can you convert this VBA with use Scripting Dictionary, so it becomes fast.

[flora]

Hi have the attached solution which works. but it is too slow.

i was wondering if there is a way to modify the code and use alternative options in VBA for example Scripting Dictionary to make this solution work faster.

Book.xlsb

[snb]

Are you familiar with pivottables ?

[flora]

Are you familiar with pivottables ?

Thanks.

Yes, i am familiar with pivot tables. this code is part of a an application that my predecessor left. i would not use pivot tables.

thanks.

[flora]

thanks very much for reply and willingness to help.

i replied to the other thread. pivot table is not an option.

thanks.

[snb]

In that case you better shouldn't use Excel at all.

[Excel Fox]

Microsoft doesn't have such compulsions about their Excel users.

Anyway, is this a sample workbook, and the original one is much larger?

[flora]

this is the sample workbook. original is more or less the same. if i can make this work, then i can update the original one as well.

thanks alot Administrator

[Excel Fox]

Here's a formula based code

Sub MacroAll()
    
    Dim lngR As Long
    lngR = Worksheets("Data").Cells(Rows.Count, 1).End(xlUp).Row
    
    With Worksheets("Main")
        With .Range(.Range("B3"), .Range("K14"))
            .FormulaR1C1 = _
            "=SUMIFS(Data!R2C4:R" & lngR & "C4,Data!R2C1:R" & lngR & "C1,VALUE(Main!R2C),Data!R2C2:R" & lngR & "C2,VALUE(Main!RC1))"
            .Value = .Value
        End With
    End With
    
End Sub

This won't work in Excel 2003 or lesser versions. Post back if you need this to work in any of those versions

[flora]

dear Administrator

thanks very much. the code is much faster and it works as i have excel 2016. it has one issue. the condition of product is missing. it ignores that. the condition on the product is that anything other than product which starts from 5, 6 , 7 should be excluded. meaning that only product ID starting with 5 6 7 should count.

is this possible with this R1C1 SUMIF?

thanks

[flora]

I have tried using =5* =6* =7* but it did not work.