Sumif in macro

[boobirajt]

Hi all

Iam new to macro. i need a help in sumif function. my input is table one

table 1
Sno	Name	qty
1	aa	10
2	bb	50
3	aa	60
4	cc	30
5	aa	10
6	cc	20
7	cc	50
8	dd	20

<tbody>
</tbody>

the out put i need is like this

table 2
Sno	Name	qty
1	aa	80
2	bb	50
3	cc	100
4	dd	20

<tbody>
</tbody>

i can do this in formule. but i need in VBA. pls help.

 

[MickG]

This code picks up on row 2 ("A2") value "SNo".
Results start F1.

[COLOR="Navy"]Sub[/COLOR] MG07Sep38
[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]
[COLOR="Navy"]Set[/COLOR] Rng = Range(Range("A2"), Range("A" & 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] Not .Exists(Dn.Offset(, 1).Value) [COLOR="Navy"]Then[/COLOR]
        n = n + 1
        Rows(n).Range("F1:H1").Value = Dn.Resize(, 3).Value
        Range("F" & n) = IIf(n > 1, n - 1, Dn.Value)
        .Add Dn.Offset(, 1).Value, n
    [COLOR="Navy"]Else[/COLOR]
        Range("H" & .Item(Dn.Offset(, 1).Value)) = Range("H" & .Item(Dn.Offset(, 1).Value)) + Dn.Offset(, 2).Value
    [COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]Next[/COLOR]
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]With[/COLOR]
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[Oeldere]

you can also use a pivot table for that kind of work.

 

[boobirajt]

This code picks up on row 2 ("A2") value "SNo".
Results start F1.

[COLOR=Navy]Sub[/COLOR] MG07Sep38
[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]
[COLOR=Navy]Set[/COLOR] Rng = Range(Range("A2"), Range("A" & 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] Not .Exists(Dn.Offset(, 1).Value) [COLOR=Navy]Then[/COLOR]
        n = n + 1
        Rows(n).Range("F1:H1").Value = Dn.Resize(, 3).Value
        Range("F" & n) = IIf(n > 1, n - 1, Dn.Value)
        .Add Dn.Offset(, 1).Value, n
    [COLOR=Navy]Else[/COLOR]
        Range("H" & .Item(Dn.Offset(, 1).Value)) = Range("H" & .Item(Dn.Offset(, 1).Value)) + Dn.Offset(, 2).Value
    [COLOR=Navy]End[/COLOR] If
[COLOR=Navy]Next[/COLOR]
[COLOR=Navy]End[/COLOR] [COLOR=Navy]With[/COLOR]
[COLOR=Navy]End[/COLOR] [COLOR=Navy]Sub[/COLOR]

Regards Mick

wow this what i am looking for and works great. Thankyou for the Help sir. Additionally i need further information.

table 1
sno	name	id no	qty
1	aa	1256	10
2	bb	1589	50
3	aa	1256	60
4	cc	1478	30
5	aa	1256	10
6	cc	1478	20
7	cc	1478	50
8	dd	1145	20

<tbody>
</tbody>

in the above table. each name has unique ID NO, which i ant in the result as like below

table 2
sno	name	id no	qty
1	aa	1256	80
2	bb	1589	50
3	cc	1478	100
4	dd	1145	20

<tbody>
</tbody>

And please Note that the result is start from F1 Cell but I need to start in F3. which is in row 3.

 

[MickG]

Try this:-

[COLOR="Navy"]Sub[/COLOR] MG07Sep32
[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]
[COLOR="Navy"]Set[/COLOR] Rng = Range(Range("A2"), Range("A" & Rows.Count).End(xlUp))
n = 2
[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] Not .Exists(Dn.Offset(, 1).Value) [COLOR="Navy"]Then[/COLOR]
        n = n + 1
        Rows(n).Range("F1:I1").Value = Dn.Resize(, 4).Value
        Range("F" & n) = IIf(n > 3, n - 3, Dn.Value)
        .Add Dn.Offset(, 1).Value, n
    [COLOR="Navy"]Else[/COLOR]
        Range("I" & .Item(Dn.Offset(, 1).Value)) = Range("I" & .Item(Dn.Offset(, 1).Value)) + Dn.Offset(, 3).Value
    [COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]Next[/COLOR]
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]With[/COLOR]
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[boobirajt]

Try this:-

[COLOR=Navy]Sub[/COLOR] MG07Sep32
[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]
[COLOR=Navy]Set[/COLOR] Rng = Range(Range("A2"), Range("A" & Rows.Count).End(xlUp))
n = 2
[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] Not .Exists(Dn.Offset(, 1).Value) [COLOR=Navy]Then[/COLOR]
        n = n + 1
        Rows(n).Range("F1:I1").Value = Dn.Resize(, 4).Value
        Range("F" & n) = IIf(n > 3, n - 3, Dn.Value)
        .Add Dn.Offset(, 1).Value, n
    [COLOR=Navy]Else[/COLOR]
        Range("I" & .Item(Dn.Offset(, 1).Value)) = Range("I" & .Item(Dn.Offset(, 1).Value)) + Dn.Offset(, 3).Value
    [COLOR=Navy]End[/COLOR] If
[COLOR=Navy]Next[/COLOR]
[COLOR=Navy]End[/COLOR] [COLOR=Navy]With[/COLOR]
[COLOR=Navy]End[/COLOR] [COLOR=Navy]Sub[/COLOR]

Regards Mick

wow you r super mick. thanks for your help. Then i need another help, can you explain me code you have given to me. because in future i will modify for my requirements.

Regards,
Boopathiraj