VBA for index match with 2 criterias

[Third Ricohermoso]

Hi,

I need help on my monitoring form.
I need to know how to make a vba code for my index-match formula.
The formula is working fine but i just noticed that it makes the file size very big as we continue to input data.

I have a control sheet wherein all the reserv and matcode and quantity are stored.
On the other sheet "data", i need the get the value of quantity based from the given reserv and matcode.

Please help.

Thank you!
__________________________________________________________________

Mod edit : thread moved to appropriate forum !​

 

[NARAYANK991]

Hi ,

If you can upload your full workbook instead of a sample having just a few rows of data , we could have tested different approaches using a formula-based solution.

Can you try this approach with your actual workbook ?

I have assumed that only one row will match , since you are using the MATCH function yourself ; if there will be more than one row which matches , my approach will not work.

Narayan

 

[Third Ricohermoso]

Hi ,

If you can upload your full workbook instead of a sample having just a few rows of data , we could have tested different approaches using a formula-based solution.

Can you try this approach with your actual workbook ?

I have assumed that only one row will match , since you are using the MATCH function yourself ; if there will be more than one row which matches , my approach will not work.

Narayan

Hi Narayan,

Thank you for the reply.
I just need to know how to transform my index-match formula into a vba code.

 

[NARAYANK991]

Hi ,

The point I was trying to make was that even if you decide to go in for VBA code , the code should implement the most efficient formula , assuming that you wish to implement the formula in your code. After all , code can possibly achieve what you want , without using any formula.

What you want is the most efficient approach , whether it is using pure formulae , or implementing a formulae-based solution using code , or using pure code without resorting to formulae.

This will only be possible if you upload your actual workbook having as much data as possible.

Clarify what you want , and if you do not wish to upload your full workbook.

Narayan

 

[Third Ricohermoso]

Hi ,

The point I was trying to make was that even if you decide to go in for VBA code , the code should implement the most efficient formula , assuming that you wish to implement the formula in your code. After all , code can possibly achieve what you want , without using any formula.

What you want is the most efficient approach , whether it is using pure formulae , or implementing a formulae-based solution using code , or using pure code without resorting to formulae.

This will only be possible if you upload your actual workbook having as much data as possible.

Clarify what you want , and if you do not wish to upload your full workbook.

Narayan

Hi Narayan,

Sorry I am trying to upload my monitoring, but seems like it was not supported. Is there other way to upload here?

Thank you!

 

[Third Ricohermoso]

Hi Narayan, sorry this is out of thread, but this is my monitoring form for last week. Thank you!

 

[NARAYANK991]

Hi ,

See if this is fast enough.

There is no code in the file for the solution , though it is a macro enabled file , because I have included some code for doing a manual recalculate.

Narayan

 

[Third Ricohermoso]

Thanks Narayan,

This is a different approach from what I expected but it helps!

Hi ,

See if this is fast enough.

There is no code in the file for the solution , though it is a macro enabled file , because I have included some code for doing a manual recalculate.

Narayan