Excel: alphanumeric if then statements

imainternforhcs Posts 1 Registration date Tuesday May 27, 2008 Status Member Last seen May 28, 2008 - May 28, 2008 at 02:56 PM
 Ayaz - Dec 10, 2009 at 03:54 AM
I am setting up a database template for people who are not Excel trained and I am attempting to make the input easy for the users as well as myself when I begin analyzing the data. I am trying to an if-then statement or create a macro (I know nothing about macros) that will allow me to have the users select their county for a list validation and the county number appear the cell to the right of the county name.
like this

County County Number
Kioskea 67856

Any advice would be helpful.

2 replies

Feb 4, 2009 at 05:30 PM
You don't need a macro for that.

You can create a validation list on a hidden worksheet that has 2 columns:
Column A contains the country names; column B contains the number values.
Then create 2 named ranges:
Range named "Country" that refers to column A only, and
Range named "Country_Codes" that refers to both columns A & B.

On your user input form, add validation to the cell that the user will input the country name.
The validation type will be a list, and in the list textbox enter "=Country" (omitting the quotes).
Make sure you check "In-Cell Dropdown"

Now for the field to the right that you want to contain the number:
Use the formula "=VLOOKUP(whatever cell has the country name,Country_Codes,2,FALSE)"

Make sure that the lookup cell is protected.
and for example if i need a macro


If Selection.Cells(x, 8) <> _
"AD" Or "AE" Or "AF" Or "AG" Or "AI" Or "AL" Or "AM" Or "AN" Or "AO" Or "AQ" Or "AR" Or "AS" Or "AT" Or "AU" Or "WS" Or "YE" Or "YT" Or "ZA" Or "ZM" Or "ZW" Then
MsgBox "Account Name cannot be empty! " & "Place: Row: " & x & " Column: 8"
GoTo Einde
End If

Why i got "run time Error: mismatch"??

Try this

If Selection.Cells(x, 8) <> "AD" Or Selection.Cells(x, 8) <>"AE" Or Selection.Cells(x, 8) <>"AF" Then
MsgBox "Account Name cannot be empty! " & "Place: Row: " & x & " Column: 8"
GoTo Einde
End If
I have worksheet with 1800 Rows and 20 Colums where ("C8 :O91") and ("C96:O179") ....etc has formula and i want to hide rows if the cell <> 0 and i want rows ("C1:O5", C92:O93", "C180) .....etc to display
Can any one help in writing macro, Please note i dont have any knowledge about macro i will just copy and paste the same in the worksheet.

Please reply Urgently.