Help to write a VB to find out total numer of Carton box , total number pf pallet, Gross Weight of the cargoes and M3 of the cargoes lot base on following fields extract from an Cargoes Export database file.
(1)
| Cargoes Receivings Tag No - Base on an unique receivings Tag No to calculate above |
(2)
| Cargoes Packing Tag No - Base on an unique Cargoes packing Tag No to calculate above |
Can we use Sumif command in the macro?
Hi David
Do you really need a macro? Can you settle for some formulas? Refer column F and G for my answers (based on the answer you supplied). Or do you only want the grand total for each?
I have no idea how to calculate the M3 and GW as you did not show how you got the figures.
Sunny
Thanks Sunny, the formula is sufficient.
Sorry, Sunny but can you help explain the meaning and logic for this formula used.
Hi David
The explanation is a bit long so hope you can understand 
COLUMN F
The formula consists of 2 parts.
Part 1
COUNTIF($C$2:C2,C2) counts how many times the Cargoes Receivings Tag No appears.
As you copy the formula down, $C$2:C2 will expand to $C$2:Cxx and it will count more and more cells and will return the number of times the tag appears within the expanding range.
Part 2
IF(COUNTIF($C$2:C2,C2)>1,0,1)
I use IF() to check whether the cell returns a value of more than 1 (i.e. duplicate)
If it is more than 1 (duplicate), then give the cell a 0.
COLUMN G
It uses the same logic as column F. The difference is to determine if it is a Carton Box or Pallet.
Since I know that C is Carton and P is Pallet and it can be found in the 14th position from the left (it is important that this position is constant otherwise the formula will fail), I then use the MID() function to get that character and append the required text using IF().
You can see the attached example for a complete breakdown on how they work.
Hope this helps.
Sunny
Deeep Deeep thanks again Sunny.
Again very very clear explanation so I grasp the tricks how it works.
