modify current VBA help

[richardlaffan]

I need to modify a current bit of code

Sub newvers()
Range("A595:C595").Copy Sheets("PRODUCT INFO").Range("A" & Rows.Count).End(xlUp).Offset(1)
Range("A595:C595").Select
Selection.ClearContents
Range("A594").Select
End Sub

I need to make sure that the content of cell A595 is not anywhere in the sheet product info,
If it does exist within product info i would like a message box to be displayed and the code to stop running

any bit of help on this is much appreciated

[JOHN H. DAVIS]

Please use code tags when posting. Maybe:

Sub richardlaffan()
Dim x As Range
Dim ws As Worksheet, ws2 As Worksheet
Set ws = ActiveSheet
Set ws2 = Sheets("PRODUCT INFO")
Set x = Sheets("PRODUCT INFO").UsedRange.Find(ws.Cells(595, "A").Value, LookIn:=xlValues, lookat:=xlWhole)
    If Not x Is Nothing Then
        MsgBox "What Do You Want The Message Too Say??"
    Else
        ws.Range("A595:C595").Copy ws2.Range("A" & Rows.Count).End(3)(2)
        ws.Range("A595:C595").ClearContents
    End If
        Set x = Nothing
End Sub

[richardlaffan]

John, you are a life saver, thanks very much, I will use tags going forward

[JOHN H. DAVIS]

You're welcome. Glad to help out and thanks for the feedback.