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.

you could define another variable, as you fill the array increment the other variable

check that variable, if its at 5 then the array is full

thanks, I did not know you could put a sheet name in front of address function

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.

suminanothersheet.xlsx

I suspected as much.

thanks Fluffinator

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))

Fluff13,

i am not using a range. this is entirely within the vba array

i want to split the columns after the array is loaded

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:

Dim sv As Variant
Dim tfn() As Variant
Dim t1()
j1=-1
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

eg:

"Doors","Y",1

"cows","M",11

"Horses","Y",111

However,

when i load the array without splitting it by col,

eg:

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?

thanks for the info.

I have no issue with loops.

other than speed. Especially dealing with .25 million records

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:

' 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"

I figured it out.

I see you actually define myArr (dim myArr)

I dont dim the split variable, since when splitting it auto defines it as an array

when i define it, it works just fine

Thanks

sorry error in my post

here's the correction

Old:

ReDim Preserve TJ(UBound(sv) - 1)

New:

ReDim Preserve TJ(UBound(TJ) - 1)

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

(the output of the split function is an array)

eg:

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)

what edit box?
seeing your spreadsheet would help (with Test data)

concat works with text values

why not simply add b2 and c2 ?

Why do you want to split a2 and then put it back in e2 ?

if you wanted it as text

=TEXT(B2,"0") & TEXT(C2,".0")

thanks Kj for your code, I will be studying it closely.
I am very well versed in VBA, however my knowledge of range.offset,intersect is limited.
Your code will expand my knowledge.
Thanks Rabsofty

I found an error in the code. sheets 9 to 12 show not exist
found error below

in Case Is = 13, I should be 1

If Not Intersect(Target, [Checks]) Is Nothing Then
        Select Case Target.Column
            Case Is = 8: i = 4
            Case Is = 11, 16: i = 2
            Case Is = 13: i = [COLOR=#0000FF]3[/COLOR]
        End Select

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

if its clicked, you can make the code make a copy of it.

normally you can hold the control key down then drag it to where you want it and it will make a copy