Posts by rabsofty

    yes I noticed a big time slow down when i upgraded from office 2010 to 2019

    I had to readjust how how my macros do things.

    I converted a lot of my macros that access the spreadsheet to arrays and that did the trick for me.

    I also had to adjust where I put my application.screenupdating in my code.

    I have a formula that works as intended.

    However when i point it to a different worksheet, it returns data in the sheet where the formula is located.

    formula in sheet1 with data in sheet1

    =ABS(SUM(INDIRECT(ADDRESS(MATCH("2 mom (4483)",$A$1:$A$37,0),3)):INDIRECT(ADDRESS(MATCH("2 mom (4483)",$A$1:$A$37,0),2+$T$7))))

    formula in sheet2 with data in sheet1

    =SUM(INDIRECT(ADDRESS(MATCH("2 mom (4483)",Sheet1!$A$1:$A$37,0),3)):INDIRECT(ADDRESS(MATCH("2 Mom (4483)",Sheet1!$A$1:$A$37,0),2+$T$7)))

    I use the formula to get the sum of the matched entry from Jan to whatever month# I put in in cell T7

    Since this formula only seems to work within the sheet you are in, can you recommend a way to do this.



    i have attached a workbook and a test file.

    put file in same location as workbook.

    open work book view logdata macro - put breakpoint where indicated

    run macro -hold mouse over each array var for contents

    do the same with logdata1 macro

    the difference is the way it extracts the columns

    logdata macro loops and extracts row by row cols 2,3,4 and puts into t1()

    logdata1 macro loops and put entire contents row by row into into t1()

    then tries to extract cols 2,3,4 from the array t1 using


    I have an array I loaded from a file







    the above is contained in an array called lnarr

    the code:

    Dim sv As Variant
    Dim tfn() As Variant
    Dim t1()
    For i = 0 To UBound(lnarr) - 1
      sv = Split(lnarr(i), ",", , 1): dt = sv(0)
      sw = awf.Index(sv, 0, Array(2, 3, 4))  'awf=application.worksheetfunction
      If dt = "I" Then j1 = j1 + 1: ReDim Preserve t1(j1): t1(j1) = sw
    Next i

    The above code does exactly what i want it to.

    it retrieve only columns 2,3,4






    when i load the array without splitting it by col,


    For i = 0 To UBound(lnarr) - 1
      sv = Split(lnarr(i), ",", , 1): dt = sv(0)
      If dt = "I" Then ac(1) = ac(1) + 1: ReDim Preserve t1(ac(1)): t1(ac(1)) = sv
    Next i

    The code above produces

    an array with the following

    t1(0)(0)= "I"

    t1(0)(1)= "Doors"

    t1(0)(2)= "Y"

    t1(0)(3)= 1

    t1(0)(4)= 2

    t1(0)(5)= 3

    t1(1)(0)= "I"

    t1(1)(1)= "cows"

    t1(1)(2)= "M"

    t1(1)(3)= 11

    t1(1)(4)= 21

    t1(1)(5)= 31

    t1(2)(0)= "I"

    t1(2)(1)= "Horses"

    t1(2)(2)= "Y"

    t1(2)(3)= 111

    t1(2)(4)= 112

    t1(2)(5)= 113

    when i try to extract columns 2,3,4 from this array,

    it only retrieves the first row (t1(0) (2,3,4))

    t2= application.worksheetfunction.index(t1,0,Array(2,3,4))
    'tried this to
    t2= application.worksheetfunction.index(t1,0,application.worksheetfunction.Transpose(Array(2,3,4)))

    it seems i can extract cols 2,3,4 when i loop through each row

    i thought application.worksheetfunction.index(t1,0,Array(2,3,4))

    should retrieve cols 2,3,4 in all rows

    What am i missing?

    I have looked into this for days.

    I am trying to get a count in a 2d array using 2 separate criteria.


    array is a dynamic array t1(x , y)


    t1(x,0) = serial date,

    t1(x,1) = function - eg: "Chg To"




    t1(x,11) = code - eg: "C01"

    Note awf = application.worksheet function


    ' counts correctly (3 "C01" 's) if only 1 criteria
      r = Application.Count(Application.Match(awf.Index(t1(), 0, 11), Array("C01"), 0)) 
    'if I use 2 criteria (code field and function field) does not even count
      r = Application.Countifs(Application.Match(awf.Index(t1(), 0, 11), 0), Array("C01"), Application.Match(awf.Index(t1(), 0, 2), 0), Array("Chg To"))

    what it should be is 2 (entries with "C01" and "Chg To")

    see sample data.

    I prefer not to use a loop!

    The reason I am now using arrays

    I had office 2010 and used to extract this data from a spread sheet and everything worked well.

    When I upgraded to office 2019, everything slowed right down.

    So I converted my data collection spread sheets that are large to arrays which greatly speeded up the data collection.

    I converted all but the one that requires 2 criteria to be counted (this one in this post)

    Any help would be appreciated.

    Sample data:

    42489.6686100694,"Chg From ","E - entry",2016,"Vnse","*",,,,"rabsofty","C06"

    42489.6686168981,"Chg To","E - entry",2016,"Weto","W",,,,"rabsofty","C08"

    42489.6694688657,"Chg From ","E - entry",2016,"Lnse","Bi",,,,"rabsofty","C06"

    42489.6694755787,"Chg To","E - entry",2016,"Lotense","Bi",,,,"rabsofty","C08"

    42489.6716525463,"Chg From ","E - entry",2016,"Wto","W",,,,"rabsofty","C06"

    42489.6716594907,"Chg To","E - entry",2016,"Wep","W",,,,"rabsofty","C08"

    42489.6722805556,"Chg From ","I - entry",2016,"Ve","*",,,,"rabsofty","C05"

    42489.6722875,"Chg To","I - entry",2016,"WInc","W",,,,"rabsofty","C07"

    42601.6604346065,"Del","E - entry",2016,"Vfr","Y",,,,"rabsofty","C04"

    42400.2868357639,"Chg To","E - entry",2016,"exp","W",,,,"rabsofty","C01"

    42400.2868357639,"Chg To","E - entry",2016,"Maxp","W",,,,"rabsofty","C01"

    42400.2868357639,"Cxx To","E - entry",2016,"Weaexp","W",,,,"rabsofty","C01"

    why can't you redim the output from a split function?

    (the output of the split function is an array)


    sv = Split(lnarr, ",", 13, 1)
    ReDim Preserve sv(UBound(sv)-1)

    Fails with invaild redim

    I did find a workaround (But I still need to know why)

    the workaround:

    Dim TJ() As String
      sv = Split(lnarr, ",", 13, 1)
      ReDim Preserve TJ(UBound(sv))
      TJ = sv
      ReDim Preserve TJ(UBound(sv) - 1)

    I looked at KjBox code and your new spreadsheet. (I like your code Kj - learned something new about range passing)

    I found the following when debugging:
    sheet MS05... did not exist
    the index within wss() does not point to the correct sheet name.
    if all boxes are empty, (srow = "") it fails on the .range hide entire row command
    if all boxes are empty, the IIF command fails as shts array is empty

    I made some changes and included them in this attached spread sheet.
    the workbook now hides and unhides the rows and the sheets.

    KjBox, I would appreciate if you could look at the code and adjust it your way (so I can learn something new)
    Thanks Rabsofty