+ Reply to Thread
Results 1 to 8 of 8

How to use Arrays in my code to shorten my script

Hybrid View

  1. #1
    Registered User
    Join Date
    12-30-2020
    Location
    Australia
    MS-Off Ver
    2016
    Posts
    16

    How to use Arrays in my code to shorten my script

    Hi there,
    Anyone please help me with my script how to minimise my current code by using Array function.
    I can able to get the results with the current code. But I have to extract results into more than 100 columns. So I need to write more than 100 lines into my script.
    For ease of understanding here I gave only very few rows of my script and attached sample excel macro sheet.

    Sum of rows values depends on criteria (Specific text in column A) need to extract into specific column range.
    For example if Text Criteria is “00” in column A then sum of values should paste into range E3, if Text Criteria is “01” in column A then sum of values should paste into range F3, if Text Criteria is “05” in column A then sum of values should paste into range J3.
    Attached Images Attached Images
    Attached Files Attached Files

  2. #2
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,623

    Re: How to use Arrays in my code to shorten my script

    One example
    Sub test()
        Dim a, i As Long, ii As Long, dic As Object
        Set dic = CreateObject("Scripting.Dictionary")
        dic.CompareMode = 1
        a = Sheets("master").Cells(1).CurrentRegion.Value
        For i = 2 To UBound(a, 1)
            a(i, 1) = "Product - " & a(i, 1)
            dic(a(i, 1)) = dic(a(i, 1)) + a(i, 2)
        Next
        With Sheets("results").Cells(1).CurrentRegion.Resize
            a = .Resize(4).Value: a(4, 4) = Application.Sum(dic.items)
            For ii = 5 To UBound(a, 2)
                a(4, ii) = dic(Trim$(a(1, ii)))
                If a(4, ii) = "" Then a(4, ii) = 0
            Next
            .Value = a
        End With
    End Sub

  3. #3
    Registered User
    Join Date
    12-30-2020
    Location
    Australia
    MS-Off Ver
    2016
    Posts
    16

    Re: How to use Arrays in my code to shorten my script

    Thank you for your response Jindon. Actual I am just learning VBA and I am not good at decoding your code. If you don't mind can you provide solution in the below format. I have multiple columns and I need to add multiple criteria. It is easy for me to update my code as per my requirement. In the below code I have achieved to get column values automatically to the desired range. But I want to add array of values for Column (1) Strings? Appreciate your help on this.

    Sub TestingThree()

    Dim ws As Worksheet
    Dim wsRES As Worksheet

    Dim irow As Integer
    Dim icolumn As Integer


    Set ws = Worksheets("Master")
    Set wsRES = Worksheets("Results")
    Worksheets("Results").Range("E2:N4").ClearContents


    For irow = 3 To 3
    For icolumn = 5 To 10

    ActiveSheet.Cells(irow, icolumn).Value = Application.SumProduct(Application.SumIfs(ws.Columns(2), ws.Columns(1), "00", ws.Columns(3), "<>DEC", ws.Columns(4), Array("2020", "2021")))

    Next icolumn
    Next irow

    End Sub

  4. #4
    Forum Expert torachan's Avatar
    Join Date
    12-27-2012
    Location
    market harborough, england
    MS-Off Ver
    Excel 2010
    Posts
    4,314

    Re: How to use Arrays in my code to shorten my script

    A loop should do it - that is if the data is no more complex than shown.

    Sub TestingTwo()
    Dim x As Long
    Dim xstr As String
    Dim ws As Worksheet, wsRES As Worksheet
    Set ws = Worksheets("Master")
    Set wsRES = Worksheets("Results")
    Worksheets("Results").Range("E2:J4").ClearContents
    For x = 0 To 5
    xstr = Format(x, "00")
    wsRES.Cells(3, x + 5) = Application.SumProduct(Application.SumIfs(ws.Columns(2), ws.Columns(1), xstr, ws.Columns(3), "<>DEC", ws.Columns(4), Array("2020", "2021")))
    Next x
    End Sub
    Attached Files Attached Files
    Torachan,

    Mission statement; Promote the use of Tables, Outlaw the use of 'merged cells' and 'RowSource'.

  5. #5
    Registered User
    Join Date
    12-30-2020
    Location
    Australia
    MS-Off Ver
    2016
    Posts
    16

    Re: How to use Arrays in my code to shorten my script

    Thank you Torachan this is the Code what I am looking for exactly. Much Appreciated.
    Thank you Jindon and very big thank you to excelForum.

  6. #6
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,623

    Re: How to use Arrays in my code to shorten my script

    But I have to extract results into more than 100 columns. So I need to write more than 100 lines into my script.
    I wouldn't use worksheet functions especially SumIfs...

  7. #7
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,623

    Re: How to use Arrays in my code to shorten my script

    If you are OK to use SumIfs then why loop?
    Sub test()
        Dim rng As Range
        Set rng = Sheets("master").Cells(1).CurrentRegion
        With Sheets("results").Cells(1).CurrentRegion
            With .Rows(4).Range("e1").Resize(, .Columns.Count - 4)
                .Formula = "=sum(sumifs(" & rng.Columns(2).Address(, , , 1) & "," & _
                    rng.Columns(1).Address(, , , 1) & ",right(e$1,2)," & rng.Columns(3).Address(, , , 1) & _
                    ",""<>dec""," & rng.Columns(4).Address(, , , 1) & ",{2020,2021}))"
                .Value = .Value
            End With
        End With
    End Sub

  8. #8
    Registered User
    Join Date
    12-30-2020
    Location
    Australia
    MS-Off Ver
    2016
    Posts
    16

    Re: How to use Arrays in my code to shorten my script

    As I said earlier I have a very basic knowledge in VBA scripting. Anyhow thank you so much for your 2x different type of codes and I will try to learn the way to implement my scripting skills with your sample codes.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Rewrite VBA script from Column based arrays to Rows based arrays
    By wtell319 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 04-11-2018, 09:24 AM
  2. [SOLVED] Shorten VBA code
    By DiCaver in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 01-09-2018, 05:40 AM
  3. [SOLVED] Represent (define?) a section of code with a variable (shorten long code lines)?
    By Gene@action in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 02-01-2016, 03:59 PM
  4. [SOLVED] Shorten Code with arrays
    By CraigsWorld in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 03-26-2015, 01:45 PM
  5. please help shorten this code
    By newbiewexcel in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 11-22-2013, 11:38 PM
  6. [SOLVED] Very inefficient code because of different sizes of arrays, how do i shorten my code?
    By Brammer88 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 12-28-2012, 04:49 PM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1