Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Help with setting a "not found" statement
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Help with setting a "not found" statement
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 wrote: 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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Help with setting a "not found" statement
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" wrote: 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 wrote: 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 . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel - Golf - how to display "-2" as "2 Under" or "4"as "+4" or "4 Over" in a calculation cell | Excel Discussion (Misc queries) | |||
how do I turn on document "editing time" found on advanced proper. | Excel Discussion (Misc queries) | |||
Why do I receive "Fill not found" message when I open Excel? | Setting up and Configuration of Excel | |||
"No RETURN() or HALT() function found on macro sheet." | Excel Worksheet Functions | |||
embedding "ISERROR" function into an "IF" statement | Excel Worksheet Functions |