PDA

View Full Version : Solved: VBA Macros Problem

10-24-2008, 12:06 AM
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

Kumar

georgiboy
10-24-2008, 12:11 AM
Welcome,

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

10-24-2008, 12:14 AM
Name in the First Column and Value in the 2 nd Column

georgiboy
10-24-2008, 12:45 AM
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

10-24-2008, 01:04 AM
Thanks . I will test and Let you know

10-24-2008, 01:31 AM
Hai georgiboy (http://www.vbaexpress.com/forum/member.php?u=15252)

Can I have your Mail ID

Thanks

georgiboy
10-24-2008, 01:33 AM
Post an example spreadsheet so i can see the full problem

10-24-2008, 01:35 AM

10-24-2008, 01:38 AM
Post an example spreadsheet so i can see the full problem

georgiboy
10-24-2008, 01:39 AM
Can you not post it on this thread then?
you should be able to scroll down to additional options and click on manage attatchments.

10-24-2008, 01:44 AM
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

10-24-2008, 01:46 AM
Once You Got the Attachment , please Tell . I want to Delete the File

10-24-2008, 01:57 AM
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
10-24-2008, 02:17 AM
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

10-24-2008, 02:27 AM
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

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
10-24-2008, 02:41 AM
Have a look at this workbook, it is the same piece of code:dunno

10-24-2008, 02:54 AM
How Can i Run this . Even I cant Open the xlsm File type

georgiboy
10-24-2008, 03:02 AM
Try this, i run Excel 2007 thats why.

10-24-2008, 03:24 AM
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
11-01-2008, 04:05 AM
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
11-01-2008, 10:42 AM
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.