| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
If Then VBA
I am trying to get the following VBA IF Then script to run and am having
mixed results. If a4 = No Then MsgBox "No Match" Exit Sub Else Supplier = InputBox("Modify Name or Code", "Company Update", Range("F5:F5000").Find(TextBox1).Value) Range("F5:F5000").Find(TextBox1).Value = Supplier Range("V5:V5000").Find(TextBox1).Value = Supplier End If End Sub Ideally, each time A4 = No, I want the the MsgBox to appear and the run to stop; conversely each time cell A4=Ok, I want the script (lines below Else) to execute. A4 represents a cell which determines if there is a match in an underlying database column (i.e. returns No if not & OK if there is in fact an exact match). Currently the first half of the script works, but, does not execute beyond. If I remove the Exit Sub I have a Run Time Error. Ive tried several iterations and feel I am close, yet have not got the end result. Could you assist me please! |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
If Then VBA
Do you have 'a4' and 'No' defined?
Or did you mean If Range("A4").Text = "No" Then ?? In article , CROD wrote: I am trying to get the following VBA IF Then script to run and am having mixed results. If a4 = No Then MsgBox "No Match" Exit Sub Else Supplier = InputBox("Modify Name or Code", "Company Update", Range("F5:F5000").Find(TextBox1).Value) Range("F5:F5000").Find(TextBox1).Value = Supplier Range("V5:V5000").Find(TextBox1).Value = Supplier End If End Sub Ideally, each time A4 = No, I want the the MsgBox to appear and the run to stop; conversely each time cell A4=Ok, I want the script (lines below Else) to execute. A4 represents a cell which determines if there is a match in an underlying database column (i.e. returns No if not & OK if there is in fact an exact match). Currently the first half of the script works, but, does not execute beyond. If I remove the Exit Sub I have a Run Time Error. Ive tried several iterations and feel I am close, yet have not got the end result. Could you assist me please! |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
If Then VBA
I did not define. I actually could use help in the area of defining.
I originally had tried Range("A4").Value = "No" Then I went on to update my script as follows: If Range("A4").Text = "No" Then MsgBox "No Match" Exit Sub End If If Range("A4").Text = "OK" Then Supplier = InputBox("Modify Name or Code", "Company Update", Range("F5:F5000").Find(TextBox1).Value) Range("F5:F5000").Find(TextBox1).Value = Supplier Range("V5:V5000").Find(TextBox1).Value = Supplier End If Is this the correct way or should I use Else? "JE McGimpsey" wrote: Do you have 'a4' and 'No' defined? Or did you mean If Range("A4").Text = "No" Then ?? In article , CROD wrote: I am trying to get the following VBA âœIF Thenâ script to run and am having mixed results. If a4 = No Then MsgBox "No Match" Exit Sub Else Supplier = InputBox("Modify Name or Code", "Company Update", Range("F5:F5000").Find(TextBox1).Value) Range("F5:F5000").Find(TextBox1).Value = Supplier Range("V5:V5000").Find(TextBox1).Value = Supplier End If End Sub Ideally, each time A4 = No, I want the the MsgBox to appear and the run to stop; conversely each time cell A4=Ok, I want the script (lines below âœElseâ) to execute. âœA4â represents a cell which determines if there is a match in an underlying database column (i.e. returns âœNoâ if not & âœOKâ if there is in fact an exact match). Currently the first half of the script works, but, does not execute beyond. If I remove the âœExit Subâ I have a âœRun Time Errorâ. Iâve tried several iterations and feel I am close, yet have not got the end result. Could you assist me please! |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
If Then VBA
In addition to my earlier response, I no longer get the MsgBox when A4 = No
"JE McGimpsey" wrote: Do you have 'a4' and 'No' defined? Or did you mean If Range("A4").Text = "No" Then ?? In article , CROD wrote: I am trying to get the following VBA âœIF Thenâ script to run and am having mixed results. If a4 = No Then MsgBox "No Match" Exit Sub Else Supplier = InputBox("Modify Name or Code", "Company Update", Range("F5:F5000").Find(TextBox1).Value) Range("F5:F5000").Find(TextBox1).Value = Supplier Range("V5:V5000").Find(TextBox1).Value = Supplier End If End Sub Ideally, each time A4 = No, I want the the MsgBox to appear and the run to stop; conversely each time cell A4=Ok, I want the script (lines below âœElseâ) to execute. âœA4â represents a cell which determines if there is a match in an underlying database column (i.e. returns âœNoâ if not & âœOKâ if there is in fact an exact match). Currently the first half of the script works, but, does not execute beyond. If I remove the âœExit Subâ I have a âœRun Time Errorâ. Iâve tried several iterations and feel I am close, yet have not got the end result. Could you assist me please! |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
If Then VBA
Your problem description is a little bit rambling but maybe this ties it down.
If I remove the Exit Sub I have a Run Time Error. I am looking at the TextBox1.Value that you are using for your find criteria. I that value is not found, then you will get a runtime error. Where is ListBox1? Is it on a sheet or a UserForm? Is the UserForm modeless, hidden, what? In short, for ListBox1.Value to be meaningful, you have to be sure it contains a value. "CROD" wrote: I am trying to get the following VBA IF Then script to run and am having mixed results. If a4 = No Then MsgBox "No Match" Exit Sub Else Supplier = InputBox("Modify Name or Code", "Company Update", Range("F5:F5000").Find(TextBox1).Value) Range("F5:F5000").Find(TextBox1).Value = Supplier Range("V5:V5000").Find(TextBox1).Value = Supplier End If End Sub Ideally, each time A4 = No, I want the the MsgBox to appear and the run to stop; conversely each time cell A4=Ok, I want the script (lines below Else) to execute. A4 represents a cell which determines if there is a match in an underlying database column (i.e. returns No if not & OK if there is in fact an exact match). Currently the first half of the script works, but, does not execute beyond. If I remove the Exit Sub I have a Run Time Error. Ive tried several iterations and feel I am close, yet have not got the end result. Could you assist me please! |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
If Then VBA
P.S. you have a couple of parenteses in the wromg place in your find
statements. "JLGWhiz" wrote: Your problem description is a little bit rambling but maybe this ties it down. If I remove the Exit Sub I have a Run Time Error. I am looking at the TextBox1.Value that you are using for your find criteria. I that value is not found, then you will get a runtime error. Where is ListBox1? Is it on a sheet or a UserForm? Is the UserForm modeless, hidden, what? In short, for ListBox1.Value to be meaningful, you have to be sure it contains a value. "CROD" wrote: I am trying to get the following VBA IF Then script to run and am having mixed results. If a4 = No Then MsgBox "No Match" Exit Sub Else Supplier = InputBox("Modify Name or Code", "Company Update", Range("F5:F5000").Find(TextBox1).Value) Range("F5:F5000").Find(TextBox1).Value = Supplier Range("V5:V5000").Find(TextBox1).Value = Supplier End If End Sub Ideally, each time A4 = No, I want the the MsgBox to appear and the run to stop; conversely each time cell A4=Ok, I want the script (lines below Else) to execute. A4 represents a cell which determines if there is a match in an underlying database column (i.e. returns No if not & OK if there is in fact an exact match). Currently the first half of the script works, but, does not execute beyond. If I remove the Exit Sub I have a Run Time Error. Ive tried several iterations and feel I am close, yet have not got the end result. Could you assist me please! |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
If Then VBA
ListBox1 is on a UserForm. It is not hidden. When you select a name from
the list, TextBox1 is populated via: Private Sub ListBox1_Click() TextBox1.Value = ListBox1.Value End Sub From there I use a Match formula (cell A3) to determine an ExactMatch (cell A1 within range V5:v200). I then use an ISNA formula to determine if Cell A4 is true or false. It all executes fine......up till the point of the IF Then in VBA. Here is my full code for this command as it exists now: Private Sub CommandButton4_Click() 'Update Existing Company If Range("A4").Text = "No" Then MsgBox "No Match" Exit Sub End If If Range("A4").Text = "OK" Then Supplier = InputBox("Modify Name or Code", "Company Update", Range("F5:F5000").Find(TextBox1).Value) Range("F5:F5000").Find(TextBox1).Value = Supplier Range("V5:V5000").Find(TextBox1).Value = Supplier End If With Worksheets("Contacts Database").Sort .SortFields.Clear .SortFields.Add Key:=Range("V5:V200"), _ SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal .SetRange Range("V4:V200") .Header = xlYes .MatchCase = False .Orientation = xlTopToBottom .SortMethod = xlPinYin .Apply End With Range("a1").Select End Sub It does run, but, I no longer have the MsgBox. I also question how I make use of 2 If Then lines. "JLGWhiz" wrote: Your problem description is a little bit rambling but maybe this ties it down. If I remove the Exit Sub I have a Run Time Error. I am looking at the TextBox1.Value that you are using for your find criteria. I that value is not found, then you will get a runtime error. Where is ListBox1? Is it on a sheet or a UserForm? Is the UserForm modeless, hidden, what? In short, for ListBox1.Value to be meaningful, you have to be sure it contains a value. "CROD" wrote: I am trying to get the following VBA IF Then script to run and am having mixed results. If a4 = No Then MsgBox "No Match" Exit Sub Else Supplier = InputBox("Modify Name or Code", "Company Update", Range("F5:F5000").Find(TextBox1).Value) Range("F5:F5000").Find(TextBox1).Value = Supplier Range("V5:V5000").Find(TextBox1).Value = Supplier End If End Sub Ideally, each time A4 = No, I want the the MsgBox to appear and the run to stop; conversely each time cell A4=Ok, I want the script (lines below Else) to execute. A4 represents a cell which determines if there is a match in an underlying database column (i.e. returns No if not & OK if there is in fact an exact match). Currently the first half of the script works, but, does not execute beyond. If I remove the Exit Sub I have a Run Time Error. Ive tried several iterations and feel I am close, yet have not got the end result. Could you assist me please! |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
If Then VBA
What did you mean when you said "Currently the first half of the script
works, but, does not execute beyond"... beyond what? Even if you take out the Exit Sub, there is no more code to be executed. Or have you hidden some of your code from us? -- Rick (MVP - Excel) "CROD" wrote in message ... I am trying to get the following VBA IF Then script to run and am having mixed results. If a4 = No Then MsgBox "No Match" Exit Sub Else Supplier = InputBox("Modify Name or Code", "Company Update", Range("F5:F5000").Find(TextBox1).Value) Range("F5:F5000").Find(TextBox1).Value = Supplier Range("V5:V5000").Find(TextBox1).Value = Supplier End If End Sub Ideally, each time A4 = No, I want the the MsgBox to appear and the run to stop; conversely each time cell A4=Ok, I want the script (lines below Else) to execute. A4 represents a cell which determines if there is a match in an underlying database column (i.e. returns No if not & OK if there is in fact an exact match). Currently the first half of the script works, but, does not execute beyond. If I remove the Exit Sub I have a Run Time Error. Ive tried several iterations and feel I am close, yet have not got the end result. Could you assist me please! |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
If Then VBA
Rick, after some more test runs I was able to get it to run........I did not
realize the referenceing to cell "A4" was CAP sensitive. That with some minor details on my end resolved things. Again, I appreciate your insight and help! "Rick Rothstein" wrote: What did you mean when you said "Currently the first half of the script works, but, does not execute beyond"... beyond what? Even if you take out the Exit Sub, there is no more code to be executed. Or have you hidden some of your code from us? -- Rick (MVP - Excel) "CROD" wrote in message ... I am trying to get the following VBA IF Then script to run and am having mixed results. If a4 = No Then MsgBox "No Match" Exit Sub Else Supplier = InputBox("Modify Name or Code", "Company Update", Range("F5:F5000").Find(TextBox1).Value) Range("F5:F5000").Find(TextBox1).Value = Supplier Range("V5:V5000").Find(TextBox1).Value = Supplier End If End Sub Ideally, each time A4 = No, I want the the MsgBox to appear and the run to stop; conversely each time cell A4=Ok, I want the script (lines below Else) to execute. A4 represents a cell which determines if there is a match in an underlying database column (i.e. returns No if not & OK if there is in fact an exact match). Currently the first half of the script works, but, does not execute beyond. If I remove the Exit Sub I have a Run Time Error. Ive tried several iterations and feel I am close, yet have not got the end result. Could you assist me please! |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Hybrid Mode
