Excel: alphanumeric if then statements

[Closed]
Report
Posts
1
Registration date
Tuesday May 27, 2008
Status
Member
Last seen
May 28, 2008
-
 Ayaz -
Hello,
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


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.
4
Thank you

A few words of thanks would be greatly appreciated. Add comment

CCM 2942 users have said thank you to us this month

and for example if i need a macro

like:

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"??

Thanks
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.

Subscribe To Our Newsletter!

The Best of CCM in Your Inbox

Subscribe To Our Newsletter!