Adding specific Rows and columns to an array

[LewisMCT]

Hi all,

Im learning some VBA to achieve a task i have so i may be asking multiple questions as i go, but first.

If i have a datasheet with rows and columns, some rows have SA and some rows have SI in column B, i want the data from 7 or 9 dependant on the value in column B and 11 irrespective.

What i have currently is as follows.

Please Login or Register  to view this content.

Im getting an error about syntax when i do this.

To ensure the correct data is being copied im also trying to print those respective arrays to other sheets.

Please Login or Register  to view this content.

This is all under a button and looks like this.

Please Login or Register  to view this content.

Any assistance is much appreciated.

[Fluff13]

How about

Please Login or Register  to view this content.

[LewisMCT]

Hi Fluff,

I tried that but i got a "Run-time error '5':"
Invalid procedure call or argument.

[Fluff13]

What line gave that error?

[LewisMCT]

This one did

Please Login or Register  to view this content.

[Fluff13]

Ok,
Do you have data in col A?
Does col B have "SA" as the entire contents of a cell?

[LewisMCT]

There is a gap on the second row? and headers in the top row? sorry.

Please Login or Register  to view this content.

[Fluff13]

OK, in that case change the line beginning Ary= to

Please Login or Register  to view this content.

[LewisMCT]

Okay cool, so it works but it only returns the top 2 results.

Is this because the code.

Please Login or Register  to view this content.

Is set to "To 2" ? If so, how can i tell it to perform this function for all rows currently with data in sheet1?

P.S. thanks btw

[Fluff13]

It should work for all the values, can you upload a workbook?

[LewisMCT]

Yes mate, here you go.

Sorry this is being such a pita.

This is just the first step of many, once they are arrayed correctly I need to figure out how to perform the validations against the values!

[Fluff13]

My mistake the last line should be

Please Login or Register  to view this content.

I've also added the Si array to go into cols D:E

[LewisMCT]

That works wonderfully thanks.

Now what i need to figure out is how to check the values within the arrays against one another.

So now that we have the correct values in SA and SI i need to see which values in SI match a value in SA and then return the total of those values that match in SI and return a field accordingly.

Array Sales Headers (2 = SA) ,9,11
Array Sales Invoices (2 = SI) ,7,11

For Sales Header 9, Search Sales Invoices 7 = 9 (11) Return Mini Invoice Array 7,11 (Where 9=7)

IF total of 11 in Mini Invoice Array = relative Sales Header array 9,11. = MATCH
IF Total of 11 in mini invoice array > relative sales header array 9,11. = GREATER VALUE
IF Total of 11 in mini invoice array < relative sales header array 9,11. = LESSER VALUE
If no row in Sales invoice matches row in sales header = NO INVOICE

Got any suggestions?

[Fluff13]

Are you only interested in the numerical values?

[LewisMCT]

Yes, basically the SA array can have multiple SI or singular SI rows that add up to the same as one of the SA rows.

Its reconciliation quite simply. I want to make the rows that match and flag errors for those that dont.

[Fluff13]

Is this of any use

Please Login or Register  to view this content.

It shows the value of SA-SI & the unaccounted invoices

[LewisMCT]

Hey mate,

That is pretty cool, though some of the data is a bit strange.

One result returned "-5.6843418860808E-14" as a value.

This actually works quite well in all other respects.

Im remiss to ask more of you, but would you mind kindly explaining how your code works so that i can better understand how its actually implemented?

As in a line by line explanation of the purpose of the code, you dont have to explain all the function parameters or whatever because i can look that stuff up. More the way that you've constructed it to work.

[Fluff13]

Please Login or Register  to view this content.

HTH

[LewisMCT]

Thanks man, that explains a lot.

[Fluff13]

You're welcome