More Descriptive Message Boxes

[Kirnon]

I need a message box which pops up when there is duplication on a spreadsheet, to be more descriptive by showing some of the duplicated information in the message. I have entered stuff but due to the order in which I am doing things I can't check if it works and since all my VBA is self taught I struggle to eyeball stuff. Can a much more learned forumite give this an eyeball and let me know if it will work and if not what I need to change?

Many thanks.

Sub New_Applicant()


Dim shp As Shape
Dim Code As String
Dim c As Range


NewRow = Sheets("ERVS").Cells(Rows.Count, 1).End(xlUp).Row + 1
Set Rng = Sheets("ERVS").Range("A" & NewRow)


Application.ScreenUpdating = False


With Sheets("ERVS").Range("A2:A" & Cells(Rows.Count, 1).End(xlUp).Row + 1)
    Set c = .Find(UserForm1.TextBox1.Value, LookIn:=xlValues)
    If Not c Is Nothing Then
            ans = MsgBox(Cells(Cell.Row, 11).Value & " " & Cells(Cell.Row, 12).Value & "'s post " & Cells(Cell.Row, 14).Value & _
            "is already on the sheet, the applications progress is " & Cells(Cell.Row, 4).Value & ". Do you want to continue?", vbYesNo + vbCritical, "Caution")
        Select Case ans
            Case 6 'Yes
                Application.Run "CopyStuff"
            Exit Sub
            Case 7 'No
            Exit Sub
        End Select
    End If
End With


Application.Run "CopyStuff"


Application.ScreenUpdating = True


End Sub

 

[Robertvk]

To be clear;
You want to check changed cells in (whole worksheet, column, row?), to see if it is a duplicate value?

And if that is true, you want to tell the user what exactly?

 

[Kirnon]

Hi Robertvk,

Thank you for responding.

This is checking a value on a userform prior to entry against the used range on a spreadsheet to see if the information is already there. The message is meant to pull information from the row that holds the potentially duplicated values and display it as part of the messagebox text.

I hope that is clear.

Kirnon

 

[Robertvk]

I would approach it something like this:

Sub dupfinder()

Dim ws As Worksheet
Dim finder As Range
Dim ans As Integer

Set ws = ActiveWorkbook.Worksheets("Sheet1")

If WorksheetFunction.CountIf(ws.Range("A:A"), UserForm1.TextBox1.Value) > 1 Then
Set finder = ws.Range("A:A").Find(UserForm1.TextBox1.Value, , xlFormulas, xlWhole, xlByRows, xlNext, False)
    If Not finder Is Nothing Then
    ans = MsgBox("Duplicate value detected!" & vbNewLine & vbNewLine & "Duplicate value found in: " & finder.Address, vbYesNo, "caution")
    Else: End If
Else: End If

End Sub

This code counts the instances of UserForm1.TextBox1.Value in column A. If there are more than 2 values (duplicates) then it will search for the first value that matches UserForm1.TextBox1.Value.

This is perhaps the first step in what you want to achieve

 

[Kirnon]

Okay,

Thanks, it's better than nothing - my previous message only stated that there was a duplicate at least this points to the location.

Thanks for your help Robertvk

 

[Kirnon]

Is it possible to display the value of a cell which is offset 3 to the right of the finder.address?

 

[Robertvk]

I'm on mobile so I can't test it, but it's probably something like:

Finder.Offset(0,3).address

 

[Kirnon]

Spot on Mate, only needed to change .address with .value and it worked a treat.

Your help has been great.

Many thanks