Duplicate Invoice code to omit N/A value

I

ipbr21054

Well-known Member
Joined
Nov 16, 2010
Messages
5,859
Office Version
  1. 2007
Platform
  1. Windows
I hope ive done this code correct but unable to confirm due to this post request.

On my worksheet i wish to check for any duplicate invoice numbers that are in column P
Also in column P will be the value N/A

Currently the code picks up this N/A as a duplicate to im unable to continue or check if code works for me.

Please advise how i cant have the code forget the value N/A below is what i have in place.

Thanks

VBA Code: 
Private Sub DuplicateChecker_Click()
    Dim Cell As Range

    With Intersect(ActiveSheet.Columns("P"), ActiveSheet.UsedRange)
        For Each Cell In .Cells
            If WorksheetFunction.CountIf(.Resize(Cell.Row - .Rows(1).Row + 1), Cell.Value) > 1 Then
             MsgBox "DUPLICATE INVOICE " & Cell.Value & "  IN CELL " & Cell.Address(False, False) & vbLf & "PLEASE CHECK THIS OUT.", vbCritical, "DUPLICATE CUSTOMER NAME FINDER"
             Cell.Select
             Exit Sub
           End If
            
        Next Cell

    End With
    MsgBox "NO DUPLICATE INVOICE NUMBERS WERE FOUND", vbInformation, "DUPLICATE INVOICE FINDER"

End Sub
 

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)
Sort by date Sort by votes
ipbr21054 said:
I hope ive done this code correct but unable to confirm due to this post request.

On my worksheet i wish to check for any duplicate invoice numbers that are in column P
Also in column P will be the value N/A

Currently the code picks up this N/A as a duplicate to im unable to continue or check if code works for me.

Please advise how i cant have the code forget the value N/A below is what i have in place.

Thanks

VBA Code: 
Private Sub DuplicateChecker_Click()
    Dim Cell As Range

    With Intersect(ActiveSheet.Columns("P"), ActiveSheet.UsedRange)
        For Each Cell In .Cells
            If WorksheetFunction.CountIf(.Resize(Cell.Row - .Rows(1).Row + 1), Cell.Value) > 1 Then
             MsgBox "DUPLICATE INVOICE " & Cell.Value & "  IN CELL " & Cell.Address(False, False) & vbLf & "PLEASE CHECK THIS OUT.", vbCritical, "DUPLICATE CUSTOMER NAME FINDER"
             Cell.Select
             Exit Sub
           End If
           
        Next Cell

    End With
    MsgBox "NO DUPLICATE INVOICE NUMBERS WERE FOUND", vbInformation, "DUPLICATE INVOICE FINDER"

End Sub
Click to expand...
If I was you I would resolve the N/A issue first.

Excel throws up an error for a reason.

#N/A is the error value that means "no value is available."

Use a zero length string instead of N/A if nothing else.

Are you still on version 2007?
 
Upvote 0
Yes i am still using excel 2007

N/A is used as no invoice number is present for which is correct.
Im not keen to use 0
The code could be written so N/A is used & ignored surely
 
Upvote 0
ipbr21054 said:
Yes i am still using excel 2007

N/A is used as no invoice number is present for which is correct.
Im not keen to use 0
The code could be written so N/A is used & ignored surely
Click to expand...
Give this a go.

NOTE THIS LINE.

Set Ws = Worksheets("Sheet1") ' <<<< Change the sheet name as appropriate.

VBA Code: 
Private Sub DuplicateChecker_Click()
Dim rngCell As Range
Dim Ws As Worksheet
Dim dict As Object
 
 Set dict = CreateObject("Scripting.Dictionary")
 
  ActiveWorkbook.Save
  
  ThisWorkbook.Activate
  
  Set Ws = Worksheets("Sheet1") ' <<<< Change the sheet name as appropriate.
  
  Ws.Activate
  
  ActiveWindow.ScrollColumn = Range("P1").Column - 4
 
    With Intersect(Ws.Columns("P"), Ws.UsedRange)
    
        For Each rngCell In .Cells
        
          If rngCell.Value <> "N/A" Then
      
            If Not dict.exists(rngCell.Value) Then
             
              dict.Add Key:=rngCell.Value, Item:=dict.Count + 1
            
            Else
            
              ActiveWindow.ScrollRow = WorksheetFunction.Max(rngCell.Row - 3, 1)
            
              rngCell.Select
              
              MsgBox "DUPLICATE INVOICE " & rngCell.Value & "  IN CELL " & _
                rngCell.Address(False, False) & vbLf & "PLEASE CHECK THIS OUT.", vbCritical, _
                "DUPLICATE CUSTOMER NAME FINDER"
              
              Exit Sub
              
            End If
            
          End If
        
        Next rngCell

    End With
        
    MsgBox "NO DUPLICATE INVOICE NUMBERS WERE FOUND", vbInformation, "DUPLICATE INVOICE FINDER"

End Sub
 
Upvote 0
Can you advise please.
My first row with values is P6 then down the page.
Ive changed this P1 to P6 but it keeps fing a match in P2.

Not sure why as P6 is the first cell with a value.
Do you see an error there.
Thanks



Code in use is shown below.

Rich (BB code): 
Private Sub DuplicateChecker_Click()
Dim rngCell As Range
Dim Ws As Worksheet
Dim dict As Object
 
 Set dict = CreateObject("Scripting.Dictionary")
 
  ActiveWorkbook.Save
  
  ThisWorkbook.Activate
  
  Set Ws = Worksheets("DATABASE")
  
  Ws.Activate
  
  ActiveWindow.ScrollColumn = Range("P1").Column - 4
 
    With Intersect(Ws.Columns("P"), Ws.UsedRange)
    
        For Each rngCell In .Cells
        
          If rngCell.Value <> "N/A" Then
      
            If Not dict.Exists(rngCell.Value) Then
             
              dict.Add KEY:=rngCell.Value, Item:=dict.count + 1
            
            Else
            
              ActiveWindow.ScrollRow = WorksheetFunction.Max(rngCell.Row - 3, 1)
            
              rngCell.Select
              
              MsgBox "DUPLICATE INVOICE " & rngCell.Value & "  IN CELL " & _
                rngCell.Address(False, False) & vbLf & "PLEASE CHECK THIS OUT.", vbCritical, _
                "DUPLICATE CUSTOMER NAME FINDER"
              
              Exit Sub
              
            End If
            
          End If
        
        Next rngCell

    End With
        
    MsgBox "NO DUPLICATE INVOICE NUMBERS WERE FOUND", vbInformation, "DUPLICATE INVOICE FINDER"

End Sub
 
Upvote 0
ipbr21054 said:
Can you advise please.
My first row with values is P6 then down the page.
Ive changed this P1 to P6 but it keeps fing a match in P2.

Not sure why as P6 is the first cell with a value.
Do you see an error there.
Thanks



Code in use is shown below.

Rich (BB code): 
Private Sub DuplicateChecker_Click()
Dim rngCell As Range
Dim Ws As Worksheet
Dim dict As Object
 
 Set dict = CreateObject("Scripting.Dictionary")
 
  ActiveWorkbook.Save
 
  ThisWorkbook.Activate
 
  Set Ws = Worksheets("DATABASE")
 
  Ws.Activate
 
  ActiveWindow.ScrollColumn = Range("P1").Column - 4
 
    With Intersect(Ws.Columns("P"), Ws.UsedRange)
   
        For Each rngCell In .Cells
       
          If rngCell.Value <> "N/A" Then
     
            If Not dict.Exists(rngCell.Value) Then
            
              dict.Add KEY:=rngCell.Value, Item:=dict.count + 1
           
            Else
           
              ActiveWindow.ScrollRow = WorksheetFunction.Max(rngCell.Row - 3, 1)
           
              rngCell.Select
             
              MsgBox "DUPLICATE INVOICE " & rngCell.Value & "  IN CELL " & _
                rngCell.Address(False, False) & vbLf & "PLEASE CHECK THIS OUT.", vbCritical, _
                "DUPLICATE CUSTOMER NAME FINDER"
             
              Exit Sub
             
            End If
           
          End If
       
        Next rngCell

    End With
       
    MsgBox "NO DUPLICATE INVOICE NUMBERS WERE FOUND", vbInformation, "DUPLICATE INVOICE FINDER"

End Sub
Click to expand...
Try this replacement code.

It was this line that started off the range in P1.
Intersect(Ws.Columns("P"), Ws.UsedRange)

VBA Code: 
Private Sub DuplicateChecker_Click()
Dim rngCell As Range
Dim Ws As Worksheet
Dim dict As Object

 Set dict = CreateObject("Scripting.Dictionary")
 
  ActiveWorkbook.Save
  
  ThisWorkbook.Activate
  
  Set Ws = Worksheets("Sheet1") ' <<<< Change the sheet name as appropriate.
  
  Ws.Activate
  
  ActiveWindow.ScrollColumn = Range("P1").Column - 4
 
        For Each rngCell In Ws.Range("P6:P" & Ws.Cells(Ws.Rows.Count, "P").End(xlUp).Row)
        
          If rngCell.Value <> "N/A" Then
      
            If Not dict.exists(rngCell.Value) Then
             
              dict.Add Key:=rngCell.Value, Item:=dict.Count + 1
            
            Else
            
              ActiveWindow.ScrollRow = WorksheetFunction.Max(rngCell.Row - 3, 1)
            
              rngCell.Select
              
              MsgBox "DUPLICATE INVOICE " & rngCell.Value & "  IN CELL " & _
                rngCell.Address(False, False) & vbLf & "PLEASE CHECK THIS OUT.", vbCritical, _
                "DUPLICATE CUSTOMER NAME FINDER"
              
              Exit Sub
              
            End If
            
          End If
        
        Next rngCell
        
    MsgBox "NO DUPLICATE INVOICE NUMBERS WERE FOUND", vbInformation, "DUPLICATE INVOICE FINDER"

End Sub
 
Upvote 0
So i now have it working & have noticed the following when using it.

I see the message box pop up & the user is advised Duplicate invoice number 411 in cell P122
Should it also advise where the other invoice number is located ?

I understand i supplied the original code but now just asking.
Thanks
 
Upvote 0
ipbr21054 said:
So i now have it working & have noticed the following when using it.

I see the message box pop up & the user is advised Duplicate invoice number 411 in cell P122
Should it also advise where the other invoice number is located ?

I understand i supplied the original code but now just asking.
Thanks
Click to expand...
It can do whatever you want but here is one way.

VBA Code: 
Private Sub DuplicateChecker_Click()
Dim rngCell As Range
Dim Ws As Worksheet
Dim dict As Object
Dim k As Variant
Dim rngPrevious As Range

 Set dict = CreateObject("Scripting.Dictionary")
 
  ActiveWorkbook.Save
  
  ThisWorkbook.Activate
  
  Set Ws = Worksheets("Sheet1") ' <<<< Change the sheet name as appropriate.
  
  Ws.Activate
  
  ActiveWindow.ScrollColumn = Range("P1").Column - 4
 
        For Each rngCell In Ws.Range("P6:P" & Ws.Cells(Ws.Rows.Count, "P").End(xlUp).Row)
        
          If rngCell.Value <> "N/A" Then
      
            If Not dict.exists(rngCell.Value) Then
             
              dict.Add Key:=rngCell.Value, Item:=rngCell.Row
            
            Else
            
              ActiveWindow.ScrollRow = WorksheetFunction.Max(rngCell.Row - 3, 1)
            
              rngCell.Select
              
              For Each k In dict.keys
                If k = rngCell.Value Then
                  Set rngPrevious = Ws.Range("P" & dict(k))
                  Exit For
                End If
              Next k
                               
              MsgBox "DUPLICATE INVOICE " & rngCell.Value & "  IN CELL " & _
                rngCell.Address(False, False) & vbCrLf & _
                "ALSO IN " & rngPrevious.Address(False, False) & vbCrLf & _
                "PLEASE CHECK THIS OUT.", vbCritical, _
                "DUPLICATE CUSTOMER NAME FINDER"
              
              Exit Sub
              
            End If
            
          End If
        
        Next rngCell
        
    MsgBox "NO DUPLICATE INVOICE NUMBERS WERE FOUND", vbInformation, "DUPLICATE INVOICE FINDER"

End Sub
 
Upvote 0
You must log in or register to reply here.

Similar threads

I
  • Question Question
Advice for slight alteration to existing working code
Replies
1
Views
265
ipbr21054
I
B
  • Question Question
VBA - Problem Highlighting Duplicate Values
Replies
3
Views
166
Fluff
Fluff
I
  • Question Question
Saved pdf from worksheet doesnt show all the page
Replies
6
Views
329
ipbr21054
I
I
  • Solved
Add date to worksheet when userform is closed
Replies
5
Views
172
ipbr21054
I
I
  • Question Question
duplicate entry restriction VBA Code causing issue in other portion
Replies
9
Views
164
Alex Blakenburg
A

Forum statistics

Threads
1,225,738
Messages
6,186,725
Members
453,368
Latest member
positivemind

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top