Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
What is wrong with my code??
I copied and edited this code to find duplicate names in my worksheet column
B and then display a msgbox to tell the user so, however it is not working at all. Please advise Sub FindDuplicates() Dim colNum As String Dim rng As Range, cell As Range Dim rng1 As Range colNum = "B" With ActiveSheet Set rng = .Range(.Cells(1, colNum), .Cells(Rows.Count, colNum).End(xlUp)) End With For Each cell In rng If Application.CountIf(rng, cell) 1 Then If rng1 Is Nothing Then Set rng1 = cell Else Set rng1 = Union(rng1, cell) End If End If Next If Not rng1 Is Nothing Then MsgBox "YOU HAVE ENTERED A DUPLICATE MANUFACTURER NAME" End If End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
What is wrong with my code??
Works for me
Replace your msgbox with this MsgBox rng1.Address "Mekinnik" wrote: I copied and edited this code to find duplicate names in my worksheet column B and then display a msgbox to tell the user so, however it is not working at all. Please advise Sub FindDuplicates() Dim colNum As String Dim rng As Range, cell As Range Dim rng1 As Range colNum = "B" With ActiveSheet Set rng = .Range(.Cells(1, colNum), .Cells(Rows.Count, colNum).End(xlUp)) End With For Each cell In rng If Application.CountIf(rng, cell) 1 Then If rng1 Is Nothing Then Set rng1 = cell Else Set rng1 = Union(rng1, cell) End If End If Next If Not rng1 Is Nothing Then MsgBox "YOU HAVE ENTERED A DUPLICATE MANUFACTURER NAME" End If End Sub |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
What is wrong with my code??
Nothing I can see ;)
You could perhaps be a bit more helpful with the message box by giving the address of the duolicate "Mekinnik" wrote: I copied and edited this code to find duplicate names in my worksheet column B and then display a msgbox to tell the user so, however it is not working at all. Please advise Sub FindDuplicates() Dim colNum As String Dim rng As Range, cell As Range Dim rng1 As Range colNum = "B" With ActiveSheet Set rng = .Range(.Cells(1, colNum), .Cells(Rows.Count, colNum).End(xlUp)) End With For Each cell In rng If Application.CountIf(rng, cell) 1 Then If rng1 Is Nothing Then Set rng1 = cell Else Set rng1 = Union(rng1, cell) End If End If Next If Not rng1 Is Nothing Then MsgBox "YOU HAVE ENTERED A DUPLICATE MANUFACTURER NAME" End If End Sub |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
What is wrong with my code??
does this do what you want? i see it was exiting only after finding the 1st
match. Sub FindDuplicates() Dim colNum As String Dim rng As Range, cell As Range Dim rng1 As Range colNum = "B" With ActiveSheet Set rng = .Range(.Cells(1, colNum), .Cells(Rows.Count, _ colNum).End(xlUp)) End With For Each cell In rng If Application.CountIf(rng, cell) 1 Then If rng1 Is Nothing Then Set rng1 = cell Else Set rng1 = Union(rng1, cell) MsgBox "YOU HAVE ENTERED A DUPLICATE MANUFACTURER NAME" _ & " (" & cell.Value & ")" End If End If Next End Sub -- Gary "Mekinnik" wrote in message ... I copied and edited this code to find duplicate names in my worksheet column B and then display a msgbox to tell the user so, however it is not working at all. Please advise Sub FindDuplicates() Dim colNum As String Dim rng As Range, cell As Range Dim rng1 As Range colNum = "B" With ActiveSheet Set rng = .Range(.Cells(1, colNum), .Cells(Rows.Count, colNum).End(xlUp)) End With For Each cell In rng If Application.CountIf(rng, cell) 1 Then If rng1 Is Nothing Then Set rng1 = cell Else Set rng1 = Union(rng1, cell) End If End If Next If Not rng1 Is Nothing Then MsgBox "YOU HAVE ENTERED A DUPLICATE MANUFACTURER NAME" End If End Sub |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
What is wrong with my code??
The code work fine in excel 2003. make sure the worksheet is the active
worksheet before you run the code and make sure tthe code is in a module VBA sheet. Close all workbooks except the active workbook Also check that your security level to make sure it is not set to high or very high. "Mekinnik" wrote: I copied and edited this code to find duplicate names in my worksheet column B and then display a msgbox to tell the user so, however it is not working at all. Please advise Sub FindDuplicates() Dim colNum As String Dim rng As Range, cell As Range Dim rng1 As Range colNum = "B" With ActiveSheet Set rng = .Range(.Cells(1, colNum), .Cells(Rows.Count, colNum).End(xlUp)) End With For Each cell In rng If Application.CountIf(rng, cell) 1 Then If rng1 Is Nothing Then Set rng1 = cell Else Set rng1 = Union(rng1, cell) End If End If Next If Not rng1 Is Nothing Then MsgBox "YOU HAVE ENTERED A DUPLICATE MANUFACTURER NAME" End If End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Can someone tell me what is wrong with this code? | Excel Discussion (Misc queries) | |||
What's wrong with my code ? | Excel Worksheet Functions | |||
What's wrong with the code,pls hv a look | Excel Programming | |||
What is wrong with this code? | Excel Programming | |||
What's wrong with this bit of code | Excel Programming |