If already exists, wont let user enter numbe

[Caveman1964]

Hi, I have a code to find a job number and then enter a date. But if the data already exists, I don't want them to be able to put anything in there. Can one of you gurus add that in this code?
thanks ahead of time

Sub Enterdatecomplaintreceived()
Dim CrrntSht As String
CrrntSht = ActiveSheet.Name
Dim JobNumber As String
Dim JobRow As Integer
Dim LastCol As Integer
Dim NewDate As String




Application.ScreenUpdating = False
Sheets("Data Collection").Activate
'Find the job number in A:A in Data Collection
JobNumber = InputBox("Please enter a job number", "Job Number")
If Trim(JobNumber) <> "" Then
With Sheets("Data Collection").Range("A:A")
Set Rng = .find(What:=JobNumber, _
After:=.Cells(.Cells.Count), _
LookIn:=xlValues, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)
If Not Rng Is Nothing Then
'job number found, find the last date in the row
Rng.Select
JobRow = ActiveCell.Row
LastCol = Cells(JobRow, Columns.Count).End(xlToLeft).Column
'ask for new date
NewDate = InputBox("Please enter the date", "Date")
'place the date
Cells(JobRow, 14).Value = NewDate
Else
MsgBox "Job number not found", vbExclamation, "Not found"
Sheets(CrrntSht).Activate
End If
End With
End If
Range("A1").Select
Sheets(CrrntSht).Activate
Application.ScreenUpdating = True
End Sub

 

[KolGuyXcel]

How about

Sub Enterdatecomplaintreceived()
Dim CrrntSht As String
CrrntSht = ActiveSheet.Name
Dim JobNumber As String
Dim JobRow As Integer
Dim LastCol As Integer
Dim NewDate As String
Application.ScreenUpdating = False
Sheets("Data Collection").Activate
'Find the job number in A:A in Data Collection
JobNumber = InputBox("Please enter a job number", "Job Number")
If Trim(JobNumber) <> "" Then
With Sheets("Data Collection").Range("A:A")
Set rng = .Find(What:=JobNumber, _
After:=.Cells(.Cells.Count), _
LookIn:=xlValues, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)
If Not rng Is Nothing Then
'job number found, find the last date in the row
rng.Select
JobRow = ActiveCell.Row
LastCol = Cells(JobRow, Columns.Count).End(xlToLeft).Column
If Cells(JobRow, 14).Value2 = "" Then
'ask for new date
NewDate = InputBox("Please enter the date", "Date")
'place the date
Cells(JobRow, 14).Value = NewDate
End If
Else
MsgBox "Job number not found", vbExclamation, "Not found"
Sheets(CrrntSht).Activate
End If
End With
End If
Range("A1").Select
Sheets(CrrntSht).Activate
Application.ScreenUpdating = True
End Sub

 

[Caveman1964]

Cool! that works....msgbox just goes away when number is already there.

 

[Caveman1964]

love it....it would be cool is a msgbox came up and said "Already Exists" and they click ok....I will study up on this and try to figure it out.
Thank yOu so much!

 

[jtakw]

Just so everyone knows, OP has multiple threads regarding the Same/Similar subject:

https://www.mrexcel.com/forum/excel...f-already-exists-will-not-let-user-enter.html

https://www.mrexcel.com/forum/excel...-ask-number-if-finds-says-already-exists.html

 

[Caveman1964]

It was two different ones and I could not get the message I wanted. My apologies if that was too much.

 

[My Aswer Is This]

Try this:

Sub Find_Me_Two()
'Modified  11/10/2018  6:23:29 PM  EST
Application.ScreenUpdating = False
Dim JobNumber As String
Dim SearchRange As Range
Dim NewDate As String
Sheets("Data Collection").Activate
JobNumber = InputBox("Please enter a job number", "Job Number")
If Len(JobNumber) < 1 Then MsgBox "No Value entered": Exit Sub
Set SearchRange = Range("A:A").Find(JobNumber)
If SearchRange Is Nothing Then MsgBox "Job number not found", vbExclamation, "Not found": Exit Sub
If Cells(SearchRange.Row, 14).Value = "" Then
    NewDate = InputBox("Please enter the date", "Date")
    Cells(SearchRange.Row, 14).Value = NewDate
    Else
    MsgBox "The Value  " & JobNumber & "   Already Exists"
End If
    
  Application.ScreenUpdating = True
End Sub

 

[Caveman1964]

Oh sweeeeeet!....yes that worked great Thank You!

 

[My Aswer Is This]

Glad I was able to help you.
Come back here to Mr. Excel next time you need additional assistance.

Oh sweeeeeet!....yes that worked great Thank You!