Help with setting a "not found" statement

[Pierre]

Hi.

I have a form that looks for a asset on a s/sheet list. What I need is a
statement that checks to see if the asset is on the list and if not, creates
a message box.

Heres the code currently:

Private Sub CommandButton1_Click()
Dim myasset As Variant
Dim Assettag As Variant
Assettag = UserForm1.TextBox1.Value


If UserForm1.TextBox1.Value > 0 Then

'Serial #
Set myasset = ThisWorkbook.Sheets("Full
listing").Columns("a").Find(What:=Assettag, LookAt:=xlWhole)
UserForm1.TextBox2.Value = myasset.Offset(0, 1).Value

'Product desc

Set myasset = ThisWorkbook.Sheets("Full
listing").Columns("a").Find(What:=Assettag, LookAt:=xlWhole)
UserForm1.TextBox3.Value = myasset.Offset(0, 2).Value

'City
Set myasset = ThisWorkbook.Sheets("Full
listing").Columns("a").Find(What:=Assettag, LookAt:=xlWhole)
UserForm1.TextBox4.Value = myasset.Offset(0, 3).Value


Any help gratefully appreciated.

Thanks

Pierre

 

[Dave Peterson]

Option Explicit
Private Sub CommandButton1_Click()
Dim myasset As Range
Dim Assettag As String

Assettag = Me.TextBox1.Value

If trim(assettag) = "" then
beep 'or add a label with a warning message????
exit sub
end if

with thisworkbook.worksheets("Full Listing")
with .Range("A:A")
set myAsset = .Range("a:a").Find(what:=Assettag, _
after:=.Cells(.Cells.Count), _
LookIn:=xlValues, _
lookat:=xlWhole, _
searchorder:=xlByRows, _
searchdirection:=xlNext, _
MatchCase:=False)
end with
end with

if myAsset is nothing then
'not found, what should happen
else
me.TextBox2.Value = myasset.Offset(0, 1).Value
me.TextBox3.Value = myasset.Offset(0, 2).Value
me.TextBox4.Value = myasset.Offset(0, 3).Value
end if

...

(all untested and uncompiled. Watch for typos!)

When you use Find in VBA, it uses the last settings that were used in the
previous find -- either by code or by the user. So it's always best to specify
all the parms of that .find.

And I'm figuring that Userform1 is the userform that holds the commandbutton and
textboxes for this process. Instead of using Userform1.textbox..., I used the
keyword Me.

Me refers to the object that owns the code. In this case, that userform. And
if you use the Me keyword, it'll be easier when you create another userform (in
this workbook or in a different workbook) and want to reuse the code.

Or if you decide that you don't like that generic UserForm1 name anymore -- when
you want something more meaningful: frmAssettDisplay

And I'm also guessing that you really didn't want to find the same assettag over
and over and over. Just use the one that was initially found.

 

[Pierre]

Thanks Dave.

Thats a really elegant solution.
One last thing I should have asked though - when it finds no asset, I would
like it to remove all the textbox data, otherwise I can be left with data
from a previous search?

Cheers

 

[Dave Peterson]

if myAsset is nothing then

me.TextBox2.Value = ""
me.TextBox3.Value = ""
me.TextBox4.Value = ""
else
me.TextBox2.Value = myasset.Offset(0, 1).Value
me.TextBox3.Value = myasset.Offset(0, 2).Value
me.TextBox4.Value = myasset.Offset(0, 3).Value
end if