Please Login or Register to view this content.
Hello,
I am trying to replace a lot of index forumlas that have started to slow my workbook down quite a lot with a macro that i hope will help me reduce "operating" time.
This setup works up untilli added another condition and another range for the match part wich i guess turned the "formula" into an array. Now i get a run-time error '13': type mysmatch. Help preety plz.
Last edited by a.hudrea; 02-10-2014 at 06:01 AM.
Tried to find some solutions and found out that for array formulas to work in vba you need to change the reference style to r1c1 so I added
but now i get and compile error: sub or function not definded and it points at region2 in the match formula.Please Login or Register to view this content.
It is not possible to join arrays using & in VBA, nor to use them in Match like this. You may either loop through ranges or create formula string to use with Evaluate method.
- Please remember to mark threads Solved with Thread Tools link at top of page.
- Please use code tags when posting code: [code]Place your code here[/code]
- Please read Forum Rules
if it's possible could you show me a small example? please
It will be like this:
Please Login or Register to view this content.
I have tried a few options, nothing worked, i only started to work with vba for 2 weeks and a lot of stuff is new to me. I have attached an example of how i would like my excel to be. In this state it works fine, but the issue is that with the current macro i only get the first item that it finds. If i want the second one i would have to add another criteria and another range in the match subthingy. If i do that it will transform it into an array and this is where things exceed my knowledge.
Book1.xlsm
Please explain what it is you want to see as output and why you may not simply use formulas.
What i need in the end is this:
- from the book1 example i want the quantity in m the I multiply it with the price that i get from another index and i get a product. That would be the ammount that i have spent for a quantity from a certain suplier. And at the very end I need to see how much i have saved (or not) by choosing on suplier over a standard price set by the board.The thing is that i have a lot of products and a lot of supliers + 12 months that results in hundreds maybe thousands of cells being populated with array formulas and that slows the workbook alot.
Thats why I am trying to setup this macro to see if it can do it faster. The workbook with formulas is already working but its slow and it will only get slow with every extra product and suplier.
You have said:
This is what I seek to clarify - I do not know what you want.with the current macro i only get the first item that it finds. If i want the second one i would have to add another criteria and another range in the match subthingy
I want the quantity of the code "222" in relation with each suplier. So from January i need the quantity of 222 from "first" suplier and the quantity of 222 from "third" suplier.
So in Sheet "Results" C6 i want the quantity of 222 from the first suplier D6 quantity of 222 from second if there is any and so on.
Last edited by a.hudrea; 02-11-2014 at 05:30 AM.
Only for code 222?
for all codes, every month and suplier but i would do that with for .. next. i will try to edit an dexample of how it looks like with formulas.
first index gets me quantity, seconde index gets me the price for that code in the month of january. I can get to the other codes, supliers and months by using for .. next.Please Login or Register to view this content.
I think your formulas will be much faster if you will create key columns instead of trying to join them inside array formulas.
I have tried, but the thing is at the end of the day i need to do it by those criterias.
I understand your criteria but if you will create one column in the source tables which concatenates the criteria fields, you will not require array formulas and they will perform better.
It may sound silly but ... i did not know you can use match with concatenate this gave me a whole new perspective on my macro :D ty so much.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks