VBA IF statements

[Joanne Toone]

I was wondering if you can do an if statement that says "if input isn't
... then ...". Below is the code im trying to sort out, and i think
you'll understand once you see it!

ActiveCell.Offset(0, 1).Range("A1").Select
ans = Application.InputBox("Enter the customer title: ", _
"Customer Title", Type:=2) ' Type 2 for a text entry
If ans = "Miss" Or ans = "Mr" Or ans = "Ms" Or ans = "Mrs"
Then
MsgBox "Please input an appropriate title", vbOKOnly +
vbCritical, "Incorrect Input"
Application.Run "InputMe"
Exit Sub
End If
ActiveCell.Offset.Range("A1") = ans

 

[Harald Staff]

Hi

I believe you want something like
If ans <> "Miss" And ans <> "Mr" And ans <> "Ms" And ans <> "Mrs" then

But my male intuition tells me that you are entering tons of personal info
this way. You should really use a userform instead of a series of inputboxes
for that. See
http://www.microsoft.com/ExcelDev/Articles/sxs11pt1.htm
for a start. (And apologies if I'm wrong ;-)

HTH. Best wishes Harald

 

[Ed]

Joanne: My 1 1/2 cents -
(1) Read the VBA Help on If-Then-Else statements, especially ElseIf, and see
if that's what you want. You might type ElseIf into the Answer Wizard and
see if the topics there help at all.
(2) What are you using Offset with Range("A1").Select? Offset moves the
selection (or active cell) from where it is to another cell relative to the
active cell - (0,1) would move it to the right one cell in the same row.
Range("A1").Select will take you from wherever you are directly to A1. You
likewise don't need it to assign ans to A1 - Range("A1") = ans will do.
(You do have Dim ans As String in the beginning of this, right?)

Ed

 

[Guest]

Have you considered using Cell Validation on the input cell?

 

[Bob Phillips]

Joanne,

You can do a

If var <> value Then

so if you want to test for something other than the list you give then

If ans <> "None" Then

as an example

You can also use Like

If ans Like "M*" Then

--

HTH

RP
(remove nothere from the email address if mailing direct)