If Then VBA

[CROD]

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!

 

[JE McGimpsey]

Do you have 'a4' and 'No' defined?

Or did you mean

If Range("A4").Text = "No" Then

??

 

[JLGWhiz]

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]

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?

 

[CROD]

In addition to my earlier response, I no longer get the MsgBox when A4 = No

 

[JLGWhiz]

P.S. you have a couple of parenteses in the wromg place in your find
statements.

 

[Rick Rothstein]

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?

 

[CROD]

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.

 

[CROD]

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!