Vba Code Required

[hrayani]

Hello,

I have data in cell S1:T7 (column S contains units and column T contains quantity)

Column Column
S T
Sets 123,456
Pcs 109,887
Pairs 123,001
Dozens 256,001
xxx 123,456
Sets 111,009

what i want in cell U1 is a total of each unit in the below format

234,465 Sets + 109,887 Pcs + 123,001 Pairs + 256,001 Dozens + 123,456 xxx

if there is no value in Column T against any unit then it should not be shown in the result cell.

is there a solution to it through a vba code ??? or an excel formula will also do

if anybody can provide a solution.

Regards,

Humayun



[TABLE="width: 166"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[hrayani]

Q) If say "S5" = "Sets" and "T5" = "290,865" and part of the result in "U1" is "290,865 Sets", and then you remove Either "Sets" and/or "29.865" from "S5/T5". Should "U1" still show "290,865 Sets" or is it removed from "U1".

Removed From U1

 

[MickG]

Try this:-
NB:- Columns swapped !!

Private [COLOR="Navy"]Sub[/COLOR] Worksheet_Calculate()
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range, Dn [COLOR="Navy"]As[/COLOR] Range, n [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] Msg [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]String[/COLOR]
[COLOR="Navy"]Set[/COLOR] Rng = Range("T2", Range("T" & Rows.Count).End(xlUp))
[COLOR="Navy"]With[/COLOR] CreateObject("scripting.dictionary")
.CompareMode = vbTextCompare
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
    [COLOR="Navy"]If[/COLOR] Dn.Value > 0 And Dn.Offset(, -1) > 0 [COLOR="Navy"]Then[/COLOR]
    [COLOR="Navy"]If[/COLOR] Not .Exists(Dn.Value) [COLOR="Navy"]Then[/COLOR]
        .Add Dn.Value, Val(Dn.Offset(, -1).Value)
    [COLOR="Navy"]Else[/COLOR]
        .Item(Dn.Value) = .Item(Dn.Value) + Val(Dn.Offset(, -1).Value)
    [COLOR="Navy"]End[/COLOR] If
    [COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]Next[/COLOR]


[COLOR="Navy"]Dim[/COLOR] K [COLOR="Navy"]As[/COLOR] Variant
 [COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] K [COLOR="Navy"]In[/COLOR] .keys
    Msg = Msg & IIf(Msg = "", Format(.Item(K), "#,##0") & " " & K & " ", "+ " & Format(.Item(K), "#,##0") & " " & K & " ")
 [COLOR="Navy"]Next[/COLOR] K
Range("U1") = Msg
[COLOR="Navy"]End[/COLOR] With
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[hrayani]

I tried the last code.

Nothing is happening Mick

 

[MickG]

Q) Where have you put the code and how are you running it.???
NB:- It's designed to run when you change the values that alter the formulas results!!

 

[hrayani]

Mick your code is working Perfect.

What i was doing is i was trying the code in a seperate sheet where i was entering the units and values manually.
Whereas, the code is designed for values which are result of formulas - as u said so.

Now its working just perfect.

Only problem is i am not able to use the undo command.....
Like Cloumn S2/T2 haveformulas (=A2/B2)... now once i enter something in (A2/B2) and press delete then i am not able to Undo that.

Any Ideas ?

Rest the code is working Great.. Thanks once again and yes thanks for the Swapped Columns.

 

[hrayani]

Hey Mick, When i finally copied to the code to the original file where i needed the code. its not working.

As soon as the value changes... It gives Run Time Error 13 - Type Mismatch

If Dn.Value > 0 And Dn.Offset(, -1) > 0 Then ​(This code is highlighted in the code)

 

[MickG]

Any chance you can post a copy of your file using "Box.com" or "DropBox"
Failing that what are the formulas in "S & T"

 

[hrayani]

the values in "S&T" are coming from different cells which are in Column E. Further those Column E cells values also contains Vlookup formulas.

Like in S2 i have "=E19"
Like in T2 i have "=E21"

 

[MickG]

I now see the formula in previous Post (16).!!!
I'll have a think.

 

[hrayani]

sure, pls