Help finding value and notify

hi

i got this sheet that I am using I have a userform which has 3 down lists and then some other textbox for our typist to fill stuff in. what i want to do is search a column and if that column was a combined value of the three drop down lists then it needs to warn the user please see the below code.

Sheets("data").Select

Columns("o:o").Select
Id = Range("x5").Value

Selection.Find(What:=Id, After:=ActiveCell, LookIn:=xlValues, LookAt:= _
xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:= _
False).Activate

ActiveCell.Offset(0, 1).Select
If ActiveCell = 1 Then
CommandButton1.Enabled = False
CommandButton5.Enabled = True
Beep
MsgBox ("You have already entered in " & court.Value & " for " & Month.Value & " " & Year.Value)
CJHours.Value = ActiveCell.Offset(0, -12).Value
DJHours.Value = ActiveCell.Offset(0, -11).Value
HCHours.Value = ActiveCell.Offset(0, -10).Value
THours.Value = ActiveCell.Offset(0, -9).Value
MHours.Value = ActiveCell.Offset(0, -8).Value
Chours.Value = ActiveCell.Offset(0, -7).Value
OHours.Value = ActiveCell.Offset(0, -6).Value
Total.Value = ActiveCell.Offset(0, -5).Value
End If

it seems to work until I choose a value which is not in the list then it comes up with run-time error 91: object variable or with block variable not set. and debugs on the below part of the code

Selection.Find(What:=courtid, After:=ActiveCell, LookIn:=xlValues, LookAt:= _
xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:= _
False).Activate


what does this mean and how do I rectify the problem?


Many thanks in advance

modify your selection,find to something like this:

Set abc = Selection.Find(What:=ID, After:=ActiveCell, LookIn:=xlValues, LookAt:= _
xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:= _
False)

If abc Is Nothing Then
MsgBox "not found"
Else
abc.Activate
End If


the above part should come in place of your selection.find... statement. What it does is that, when the value is not in the list it gives a message saying not found, otherwise it does what it is supposed to. You may remove the msgbox and put whatever you want in its place.

- Mangesh

- Mangesh

thanks it works if i want it to say that it was not found but what i wanted was it to warn the typist that it has been entered in so that they know that they will be overwriting data.

I tried modiefying your code to the below

Columns("o:o").Select
Id = Range("x5").Value

Set abc = Selection.Find(What:=Id, After:=ActiveCell, LookIn:=xlValues, LookAt:= _
xlWhole, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:= _
False)

If abc Is Nothing Then
' MsgBox "not found"

Else
abc.Activate
MsgBox "not found"
End If

some reason it finds the value but still says not found.

any ideas??

some reason it finds the value but still says not found.

It says not found because you placed the msgbox which gives this message in the else part. If you want to warn the typist, then change the if then to somthing like this:

If abc Is Nothing Then
' MsgBox "not found"

Else
abc.Activate
MsgBox "The value exists"
End If

Note: I have just changed the message in the message box.

- Mangesh

of coarse logical answer cheers mate. been bashing my head against a brick wall try to solve it but was looking to in detail.