Results 1 to 10 of 10

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

  1. #1
    Junior Member
    Join Date
    Nov 2017
    Posts
    21
    Rep Power
    0

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

    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

  2. #2
    Senior Member
    Join Date
    Jun 2012
    Posts
    337
    Rep Power
    12
    Are you familiar with pivottables ?

  3. #3
    Junior Member
    Join Date
    Nov 2017
    Posts
    21
    Rep Power
    0
    Quote Originally Posted by snb View Post
    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.

  4. #4
    Junior Member
    Join Date
    Nov 2017
    Posts
    21
    Rep Power
    0
    thanks very much for reply and willingness to help.

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

    thanks.

  5. #5
    Senior Member
    Join Date
    Jun 2012
    Posts
    337
    Rep Power
    12
    In that case you better shouldn't use Excel at all.

  6. #6
    Administrator Excel Fox's Avatar
    Join Date
    Mar 2011
    Posts
    1,401
    Rep Power
    10
    Microsoft doesn't have such compulsions about their Excel users.

    Anyway, is this a sample workbook, and the original one is much larger?
    A dream is not something you see when you are asleep, but something you strive for when you are awake.

    It's usually a bad idea to say that something can't be done.

    The difference between dream and aim, is that one requires soundless sleep to see and the other requires sleepless efforts to achieve

    Join us at Facebook

  7. #7
    Junior Member
    Join Date
    Nov 2017
    Posts
    21
    Rep Power
    0
    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

  8. #8
    Administrator Excel Fox's Avatar
    Join Date
    Mar 2011
    Posts
    1,401
    Rep Power
    10
    Here's a formula based code

    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
    A dream is not something you see when you are asleep, but something you strive for when you are awake.

    It's usually a bad idea to say that something can't be done.

    The difference between dream and aim, is that one requires soundless sleep to see and the other requires sleepless efforts to achieve

    Join us at Facebook

  9. #9
    Junior Member
    Join Date
    Nov 2017
    Posts
    21
    Rep Power
    0
    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

  10. #10
    Junior Member
    Join Date
    Nov 2017
    Posts
    21
    Rep Power
    0
    I have tried using =5* =6* =7* but it did not work.
    Last edited by flora; 11-28-2017 at 05:03 AM.

Similar Threads

  1. VBA Macro To Convert Text To Proper Case
    By Howardc in forum Excel Help
    Replies: 4
    Last Post: 05-31-2013, 12:38 AM
  2. Need VBA code to convert csv to xlsx and vice versa
    By Pravee89 in forum Excel Help
    Replies: 1
    Last Post: 10-13-2012, 11:31 PM
  3. Vba scripting dictionary help
    By bpascal123 in forum Excel Help
    Replies: 1
    Last Post: 07-14-2012, 10:52 AM
  4. Excel VBA Dictionary Object
    By Rajan_Verma in forum Rajan Verma's Corner
    Replies: 1
    Last Post: 05-13-2012, 10:01 PM
  5. Convert Series into Range.
    By ayazgreat in forum Excel Help
    Replies: 13
    Last Post: 03-26-2012, 08:49 PM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •