Solved: VBA Macros Problem

[yapmadhu]

Hai

I am New to VB Macros . I want to Check the Duplicate Value in the Excel Value Using Macro VB editor

Eg:

If the 1 St Row Contains Name as " VB" and the Value For Vb is 30 ( Name in the 1 St Column and Value in the 2 nd Column)
2 nd Row Contains the Same Name "VB" and the Value for this is 20.

I want to Remove the Duplicate Name and I want the output like this

VB and the Value is 50 (30+20)

Can Anyone Tell How can I Get this? Can AnyOne Send me the Code for Adding the Value After Checks the Duplicate Name

I Written One Method that Generates the Values in the AboveSheet. I want to Check the Duplicate Value in the Sheet

Thanks in Advance

Kumar

[georgiboy]

Welcome,

Is the name and value in the same column or is the name in column "A" and value in column "B" for example?

[yapmadhu]

Name in the First Column and Value in the 2 nd Column

[georgiboy]

This should do it, there may be an easier way but this is what i came up with

Sub Cons()
Dim rCell As Range, LastRow As Integer
Dim r As Long, N As Long, MyRange As Range
Dim V As Variant

N = 0
LastRow = Range("A" & Rows.Count).End(xlUp).Row
Set MyRange = Range("A1:A" & LastRow)

On Error Resume Next
For Each rCell In MyRange.Cells
rCell.Offset(, 1).Value = WorksheetFunction.SumIf(Range("A:A"), rCell.Value, Range("B:B"))
Next


For r = MyRange.Rows.Count To 1 Step -1
V = MyRange.Cells(r, 1).Value
If Application.WorksheetFunction.CountIf(MyRange.Columns(1), V) > 1 Then
MyRange.Rows(r).EntireRow.Delete
N = N + 1
End If
Next r

End Sub
Hope this helps

[yapmadhu]

Thanks . I will test and Let you know

[yapmadhu]

Hai georgiboy (http://www.vbaexpress.com/forum/member.php?u=15252)

Can I have your Mail ID

Thanks

[georgiboy]

Post an example spreadsheet so i can see the full problem

[yapmadhu]

Please Send your Mail ID. SO that i wil Send the Spread Sheet

[yapmadhu]

Post an example spreadsheet so i can see the full problem

[georgiboy]

Can you not post it on this thread then?
you should be able to scroll down to additional options and click on manage attatchments.

[yapmadhu]

Hai Just look at the Attachment .

I want to Check the Duplicate Vendor in the Occupancy Report Sheet and I want to Add the Column 3,5,7,9,10 as in the Code

[yapmadhu]

Once You Got the Attachment , please Tell . I want to Delete the File

[yapmadhu]

Can you not post it on this thread then?
you should be able to scroll down to additional options and click on manage attatchments.
Did U got that WorkSheet

[georgiboy]

I do not fully understand the sheet but this should clarify a few things

Sub Cons()
Dim rCell As Range, LastRow As Integer
Dim r As Long, N As Long, MyRange As Range
Dim V As Variant

N = 0
LastRow = Range("A" & Rows.Count).End(xlUp).row
Set MyRange = Range("A10:A" & LastRow)

On Error Resume Next
For Each rCell In MyRange.Cells
rCell.Offset(, 2).value = WorksheetFunction.SumIf(Range("A:A"), rCell.value, Range("C:C"))
rCell.Offset(, 4).value = WorksheetFunction.SumIf(Range("A:A"), rCell.value, Range("E:E"))
rCell.Offset(, 6).value = WorksheetFunction.SumIf(Range("A:A"), rCell.value, Range("G:G"))
rCell.Offset(, 8).value = WorksheetFunction.SumIf(Range("A:A"), rCell.value, Range("I:I"))
rCell.Offset(, 10).value = WorksheetFunction.SumIf(Range("A:A"), rCell.value, Range("K:K"))
Next


For r = MyRange.Rows.Count To 1 Step -1
V = MyRange.Cells(r, 1).value
If Application.WorksheetFunction.CountIf(MyRange.Columns(1), V) > 1 Then
MyRange.Rows(r).EntireRow.Delete
N = N + 1
End If
Next r

End Sub

Hope this helps

[yapmadhu]

Hai Just Open the First WorkSheet

In Row 10and 11 the Same Name Appears.I want to Remove the Duplicate Value After it Adds the Values

For Eg :

Row 10
name "Vendor1" value(Column 3)=10 , Value(Column 5) =20

Row 11
name "Vendor1" value(Column 3)=10 , Value(Column 5) =20

I want the Output like this

Vendor 1 with values in the Column 3 is (10+10)= 20 and Values in the Column 5 (10+10) is 20

Please Help me
Thanks



I do not fully understand the sheet but this should clarify a few things

Sub Cons()
Dim rCell As Range, LastRow As Integer
Dim r As Long, N As Long, MyRange As Range
Dim V As Variant

N = 0
LastRow = Range("A" & Rows.Count).End(xlUp).row
Set MyRange = Range("A10:A" & LastRow)

On Error Resume Next
For Each rCell In MyRange.Cells
rCell.Offset(, 2).value = WorksheetFunction.SumIf(Range("A:A"), rCell.value, Range("C:C"))
rCell.Offset(, 4).value = WorksheetFunction.SumIf(Range("A:A"), rCell.value, Range("E:E"))
rCell.Offset(, 6).value = WorksheetFunction.SumIf(Range("A:A"), rCell.value, Range("G:G"))
rCell.Offset(, 8).value = WorksheetFunction.SumIf(Range("A:A"), rCell.value, Range("I:I"))
rCell.Offset(, 10).value = WorksheetFunction.SumIf(Range("A:A"), rCell.value, Range("K:K"))
Next


For r = MyRange.Rows.Count To 1 Step -1
V = MyRange.Cells(r, 1).value
If Application.WorksheetFunction.CountIf(MyRange.Columns(1), V) > 1 Then
MyRange.Rows(r).EntireRow.Delete
N = N + 1
End If
Next r

End Sub
Hope this helps

[georgiboy]

Have a look at this workbook, it is the same piece of code:dunno

[yapmadhu]

How Can i Run this . Even I cant Open the xlsm File type

[georgiboy]

Try this, i run Excel 2007 thats why.

[yapmadhu]

Have a look at this workbook, it is the same piece of code:dunno
Thanks a Lot . I got It . You Send me the Code , what i want Exactly

[Aussiebear]

Kumar, please don't ask members here for their email addresses. This is a public forum. If you have an issue that is commercially sensitive then get someone to assist you offline, but be prepared to be asked to contribute something for the work undertaken.

[lucas]

Once You Got the Attachment , please Tell . I want to Delete the File

You are close to being banned from our friendly forum my friend. Deleting files posted is frowned upon.

If it is proprietary information then you need to provide a clean workbook without the sensitive data.

If you need personal assistance for a complex project, professional help for pay is available here.

I have some small reason to believe that this might be a class assignment and if you think that deleting the file will keep your instructor from finding out then I have a very low opinion of that kind of behavior.

You must deal with Aussibear from this point forward and if he is not happy with your compliance to our very lenient procedures here then your username will be banned.