VBA to trigger input box when criteria in data founded

[Addictions]

This is a bit complicated and I am trying my best to explain it.

I am trying to find a VBA code which triggers input box when specific criteria is found in data.

The data has a transaction codes and when criteria from columns A,B,C match it means it is 1 delivery.
Criteria for a delivery is (Part 1,Identify, PERM-CRE-LOC).

ColumnA	ColumnB	ColumnC
Part number	Activity code	Operation code
Part1	Piece-pick	PERM-CRE-LOC
Part1	Identify	PERM-CRE-LOC
Part1	General-movement	PERM-ADJ-LOC
Part1	Identify	PERM-CRE-LOC

By using this formula it returns a value of 2: COUNTIFS($A:$A,""Part 1"",$B:$B,""Identify"",$C:$C,""PERM-CRE-LOC"") . Which means I had two deliveries.
Is there a way a VBA can do this search and when it finds the matched criteria(delivery) then it triggers the input box?

I would like input box to give two options:
1st type in the quantity and 2nd to type in the quantity. Then the typed quantity placed in a different worksheet in specific range? i.e "Worksheet1" range A2 for 1st input and range B2 for 2nd input.
Can input box also display the number of total found criterias (Deliveries)?. Please help.

Many thanks

[Pango]

Try:

Please Login or Register  to view this content.

[Addictions]

Hello,

Thank you for a quick reply. This is great it does everything I asked for.

I have one more question. I am going to have about 30 different part numbers. Should I repeat this code for each part? or there is a way to add more lines somehow?.


Thank you very much

[Pango]

Is it just the part numbers that will change or will the other criteria for delivery change as well? (i.e. to count as a delivery, does Part2 use the same values in B and C or are they different?).

Also, what do you want to happen - do you need separate counts per part type, or do they all add together into DeliveryCount?

[Addictions]

The parts are going to change only. It is going to be the same criteria for each part. I would need also to seperate each count for each part with input box seperated for them. The input box results would go to a different cells as well. Thank you for your help

[Addictions]

Hi Pango,

Do you think it is possible to make it?.

Regards

[Pango]

It sort of feels like the COUNTIFS function might be more appropriate for that - having 30 unique counts in a macro would be a mess.

Surely it's easier to have a COUNTIF for each part, and then you can see the DeliveryCount for each, and just type in the Quantity and Quality (like you're doing anyway) next to that? If you don't want to have the count visible after you've done it, either do it on a working sheet, and just copy the Quantity and Quality over, or just delete it after you've done it.


To modify the macro, you'd have to declare variables for each of your parts (the count):

Please Login or Register  to view this content.

...and then do something like:

Please Login or Register  to view this content.

And then you'd need to do this:

Please Login or Register  to view this content.

...30 times, once for each part type, including modifying where the Quality and Quantity paste themselves.

Like, you can do it, but as far as I can tell it will be a multi-hundred line mess...

[Addictions]

Thank you very much for help, I am going definitely to try this out.