PDA

View Full Version : Solved: Code for not found



Gil
03-10-2010, 05:33 PM
Hello

I have the following code which works well but in the rare event the data is not found then the code stops. Can anyone suggest what I need to add to return a 'Not Found' result.



Sub Find ()
Dim Sh As Worksheet
Dim Fnd As Range
Dim c As Range


Set Sh = Sheets("Sheet1")
Set c = ActiveCell
Set Fnd = Sh.Cells.Find(Trim(Split(c)(0)), LookAt:=xlWhole)
c.Offset(, 1) = Sh.Cells(2, Fnd.Column - 0) & "-" & Fnd.Offset(, -1) & "-" & Fnd.Offset(, -2)
End Sub

Gil

lucas
03-10-2010, 06:08 PM
What would the cell look like if it would be not found?

What would be a test string?

Gil
03-10-2010, 06:35 PM
Instead of a result such as 1-6-55 I would like to see 'Not Found' or 'Not Found'-'Not Found'-'Not Found'.
Gil

lucas
03-10-2010, 06:43 PM
Gil, I don't have enough info to follow what you are doing. I put this together thinking you mean if the two cells to the left were blank:

Sub Find()
Dim Sh As Worksheet
Dim Fnd As Range
Dim c As Range


Set Sh = Sheets("Sheet1")
Set c = ActiveCell
Set Fnd = Sh.Cells.Find(Trim(Split(c)(0)), LookAt:=xlWhole)
If c.Offset(, -1) = "" Or c.Offset(, -2) = "" Then
MsgBox "not found"
Else
c.Offset(, 1) = Sh.Cells(2, Fnd.Column - 0) & "-" & Fnd.Offset(, -1) & "-" & Fnd.Offset(, -2)

End If
End Sub

domfootwear
03-10-2010, 06:50 PM
Instead of a result such as 1-6-55 I would like to see 'Not Found' or 'Not Found'-'Not Found'-'Not Found'.
Gil

Try this code:


Sub Find()
Dim Sh As Worksheet
Dim Fnd As Range
Dim c As Range

On Error GoTo ErrHandler:
Set Sh = Sheets("Sheet1")
Set c = ActiveCell
Set Fnd = Sh.Cells.Find(Trim(Split(c)(0)), LookAt:=xlWhole)
c.Offset(, 1) = Sh.Cells(2, Fnd.Column - 0) & "-" & Fnd.Offset(, -1) & "-" & Fnd.Offset(, -2)

Exit Sub

ErrHandler:
MsgBox "Not found !!!"


End Sub

lucas
03-10-2010, 06:52 PM
dom, we will have to let Gil weigh in and let us know, but I don't think it throws an error.

lucas
03-10-2010, 07:20 PM
gil, maybe you could plug in some data that will give us some idea.

I select either of the highlighted cells in the attachment and run it, one at a time of course to get what I thought you were trying to do.

Clarification would help immensely.

mdmackillop
03-11-2010, 10:54 AM
Sub Find()
Dim Sh As Worksheet
Dim Fnd As Range
Dim c As Range


Set Sh = Sheets("Sheet1")
Set c = ActiveCell
Set Fnd = Sh.Cells.Find(Trim(Split(c)(0)), LookAt:=xlWhole)
If Not Fnd Is Nothing Then
c.Offset(, 1) = Sh.Cells(2, Fnd.Column - 0) & "-" & Fnd.Offset(, -1) & "-" & Fnd.Offset(, -2)
Else
MsgBox "Not Found"
End If
End Sub

Gil
03-11-2010, 11:02 AM
Hello
I have tried both codes and adapted each slightly. I have changed Msg box to c.Offset(, 1) = "Not found !!!" and that puts it in cell like a found result.
I have also added a Do Loop & changed the font and colour.

Both stop at the point where Not Found happens. I have tweaked and so on but got nowhere.Your further help would be appreciated.
Lucas code in Mod 4
Domfootwear code in Mod 2

Gil
03-11-2010, 11:07 AM
Hello
I have tried both codes and adapted each slightly. I have changed Msg box to c.Offset(, 1) = "Not found !!!" and that puts it in cell like a found result.
I have also added a Do Loop & changed the font and colour.

Both stop at the point where Not Found happens. I have tweaked and so on but got nowhere.Your further help would be appreciated.
Lucas code in Mod 4
Domfootwear code in Mod 2

Gil

Sorry
I have been so busy working with Lucas's & Domfootwear's code i missed the last post from MdMackillop. Please accept my appologies.
Gil

Gil
03-11-2010, 12:54 PM
Hello
Many thanks to you all. With the entry from mdmackillop I have resolved my problem.
I have attached the file if any one is interested.
Use module 1

mdmackillop
03-11-2010, 04:39 PM
I would suggest a similar code for any Find routine to deal with the "Not Found" scenario
i.e,

Set Fnd = Sh.Cells.Find("Whatever")
If Not Fnd Is Nothing Then