return lbound and ubound of 2d array

so here is my code, it works in that it creates the 2d array. now i need to return the lbound and ubound of the array but having problems:

Please Login or Register  to view this content.

i tried this:

Please Login or Register  to view this content.

but it didn't print to the immediate window. what am i doing wrong?

also tried this:

Please Login or Register  to view this content.

but again, nothing prints to the window

In next statement

Please Login or Register  to view this content.

is missing an argument
see
https://docs.microsoft.com/fr-fr/off...bound-function

You're trying to access the boundaries of an element of the array, but you need the array itself.

Lbound(arr, 1) and UBound(arr, 1)

for the first dimension and replace the 1 by 2 for the second dimension.

However, your code won't work at all since you can only resize the upper boundary of the last dimension if you use Preserve with ReDim.

basically i have text on sheet (range I8) and i want the lowerbounds of the second argument to the array to be a specific row number (8 in this case) and the upperbounds of second array is (10)

my array looks like this: arr(1,0)="1005" and arr(1,1)=8
as well as: arr(3,0)="1015" and arr(3,1)=10

so the second array number is the row number of the selected text and that is what i want returned (so i can sum up the rows based on arr(x,0) and put that in a cell). does any of this help?

That has nothing to do with the title of your question. That's just returning a value from an array. To be honest, I can't really see why you need an array at all here since it seems you just want a start and stop row number.

Probably. Why don't you describe what you're actually trying to achieve, in plain English?

so i have this trial balance macro that i am writing. it looks in column I for text that is surround by parenthesis and grabs the associated row numbers to sum them up.

so in column A i have the following:

A8 is "1005 Operating"
A9 is "1010 Payroll"
A10 is "1015 Petty Cash"

the text is I8 is "Cash (1000-1099)"
so i need to capture the row number for all accounts that are in the range of (1000-1099)

so my macro reads the text in I8 and parses it so include all accounts in the range. note that A8 does not start with 1000 and A10 does not end in 1099 but need to sum them up, like so: Range("I8").formula = "=sum(A8:A10)"

note that this is just one of the many accounts so i include rows 8 to 22 in row I and match all rows and put them in a sum. does that help?

A couple of questions then:
-Are the accounts always in order, or could you end up with discontiguous ranges that you need to sum?
-Do you actually need a formula, or just the result?

In the spirit of not reinventing the wheel, what you are describing sounds to me like a task that a pivot table or a SUMIFS() function would do. I would be curious why you are not using these existing tools to do this task.

In that case all you need is startRow and endRow variables that you can assign inside your loop. If the criteria are met and startRow is 0 (its initial value) assign the current row to it. If startRow is not 0, then assign the row to endRow and continue until the criteria aren't met anymore.

any help? would be very appreciative.

here is the sample workbook that you can review, the procedure is named "test_acct2"

Not sure if you decided that this must be VBA. Here's how I did it with a SUMIFS() function:

1) I need the account numbers out of the text string. I'm not very good with text manipulation functions, but it was pretty easy to do manually. I enter 1000 in P8, 1099 in Q8, P9 is 1100, Q9 is 1199, and so on. For rows where there is only one account number like row 13, enter that account number into both P and Q. I'm sure if you must have a formula for this step, the guys here can help with a text manipulation formula that will extract the lower and upper account numbers from the text in I.
2) From there it's a simple SUMIFS() function. In J8 =SUMIFS(B$8:B$144,$A$8:$A$144,">="&$P8,$A$8:$A$144,"<="&$Q8). Note the mix of relative and absolute references for easy copying, then copy/paste/fill into J8:L22.

Unless there is some external requirement to use VBA, that should work.

I find that spreadsheets work best when each cell contains only one piece of information -- in this case, either the account number or the account description. My solution would be to add a helper column to the main database that extracts the account number from the account number & description field. If the account number is always the first 4 characters, then this might be something like =VALUE(LEFT(A8,4)). The LEFT() function extracts the first 4 characters from the text string, and the VALUE() function converts the number as text string to a real number. The criteria ranges in the SUMIFS() function would then point to this helper column.

got that working by doing this:

Please Login or Register  to view this content.

now i am trying to get the code in parenthesis and having an issue with this one line, what am i doing wrong?

Please Login or Register  to view this content.

so got that going by doing this:

Please Login or Register  to view this content.

On to the next journey

Glad you are making progress.

One thing I would note -- because it often makes this kind of programming (writing excel formulas into a range of cells) simpler to program and faster to execute -- many learn to write formulas to cells from VBA using R1C1 notation. This often simplifies the programming because the R1C1 formula text string does not change from cell to cell. For example, your loop in post 21 can be replaced by the single statement

Please Login or Register  to view this content.

If you are in the mood to learn a little R1C1 notation, you may find that using R1C1 notation simplifies many of these VBA steps.

so now im trying to write the sumifs on the consolidation table and getting an application error when i try this one line, what am i doing wrong?

Please Login or Register  to view this content.

MrShorty, can you help me with this. i think once this is done, i can finish this project.

fyi, here is the code without being in a formula format

Please Login or Register  to view this content.

what am i doing wrong. can this be converted to R1C1 format?

I expect that it is not an A1/R1C1 kind of thing. I expect that it has to do with the quotation marks that are in the criteria arguments. And, since I never really use VBA to add formulas to cells, I am not very good at getting the right number and placement of quotation marks in these kinds of things. I know we have others on here who are good at this sort of thing.

try:

Please Login or Register  to view this content.

so now i need two more helper rows, one for the firstrow and one for the lastrow in each account category. can't use sumifs, not the right command. what should i use to get the row numbers?

cell r8 should look at the 4 digits in column a and see if it's in the range of p8 and get the first row number and should cycle through column a and search for the lastrow in column q to get the last row number.
then cycle through r9 through r22. how can that be done?

let me know if you want me to post an upload of the workbook.

so i researched the IFS command and that seems to be what i need for this portion of the code.

need something like this:

Please Login or Register  to view this content.

how can that be done (the above doesn't work, i tried)

the portion of the activecell really needs to be row number that is returned.

Hi, Dan!

You complained that there has been "no traction" on this thread. Unfortunately, and I suspect this is relevant, you have moved onto a new issue here, and for this, you should have started a new thread. I strongly suggest you mark this thread as SOLVED and start a new thread with an appropriately-worded title that will attract the help you need.