Posts by rabsofty

    Fluff13,


    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


    awf.index(t1,0,Array(2,3,4))

    Files

    • slice2d.xlsm

      (22.7 kB, downloaded 62 times, last: )
    • logs.txt

      (109 Byte, downloaded 62 times, last: )

    I have an array I loaded from a file


    EG:

    "I","Doors","Y",1,2,3

    "I","cows","M",11,21,31

    "I","Horses","Y",111,112,113

    "E","Swift","Y",14,24,34

    "E","John","Y",18,28,38


    the above is contained in an array called lnarr


    the code:

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


    The above code does exactly what i want it to.

    it retrieve only columns 2,3,4


    eg:


    "Doors","Y",1

    "cows","M",11

    "Horses","Y",111


    However,

    when i load the array without splitting it by col,

    eg:

    Code
    1. For i = 0 To UBound(lnarr) - 1
    2.   sv = Split(lnarr(i), ",", , 1): dt = sv(0)
    3.   If dt = "I" Then ac(1) = ac(1) + 1: ReDim Preserve t1(ac(1)): t1(ac(1)) = sv
    4. 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))


    Code
    1. t2= application.worksheetfunction.index(t1,0,Array(2,3,4))
    2. 'tried this to
    3. 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.


    eg:

    array is a dynamic array t1(x , y)


    contains:

    t1(x,0) = serial date,

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

    .

    .

    .

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


    Note awf = application.worksheet function


    Eg:

    Code
    1. ' counts correctly (3 "C01" 's) if only 1 criteria
    2.   r = Application.Count(Application.Match(awf.Index(t1(), 0, 11), Array("C01"), 0)) 
    3. 'if I use 2 criteria (code field and function field) does not even count
    4.   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)


    eg:

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

    Fails with invaild redim


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


    the workaround:


    Code
    1. Dim TJ() As String
    2.   sv = Split(lnarr, ",", 13, 1)
    3.   ReDim Preserve TJ(UBound(sv))
    4.   TJ = sv
    5.   ReDim Preserve TJ(UBound(sv) - 1)

    Storm,
    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

    Files

    • Book2.xlsm

      (71.77 kB, downloaded 59 times, last: )

    this is how I would do it (there are other ways to do this)