If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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 . |
#4
|
|||
|
|||
Help with setting a "not found" statement
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 Pierre wrote: 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 . -- Dave Peterson |
Thread Tools | |
Display Modes | |
|
|