There are a couple of ways you can approach this ...
Possible Solution # 1
-------------------------
If your criteria must be hardcoded in the VBA procedure then ...
Select Case Selection.Cells(x&, 8).Value
Case "AD", "AE", "AF", "AG", "AI", "AL", "AM", "AN", "AO", "AQ",
"AR", "AS", "AT", "AU", "AW", "AX", "AZ", _
"BA", "BB", "BD", "BE", "BF", "BG", "BH", "BI", "BJ", "BM",
"BN", "BO", "BR", "BS", "BT", "BV", "BW", "BY", "BZ", _
"CA", "CC", "CD", "CF", "CG", "CH", "CI", "CK", "CL", "CM",
"CN", "CO", "CR", "CS", "CU", "CV", "CX", "CY", "CZ", _
"DE", "DJ", "DK", "DM", "DO", "DZ", _
"EC", "EE", "EG", "EH", "ER", "ES", "ET", _
"FI", "FJ", "FK", "FM", "FO", "FR", _
"GA", "GB", "GD", "GE", "GF", "GG", "GH", "GI", "GL", "GM", _
"WS", "YE", "YT", "ZA", "ZM", "ZW"
' Do nothing
Case Else
MsgBox "Account Name cannot be empty ! " & "Place: Row: " &
x& & " Column: 8"
End Select
Possible Solution # 2
-------------------------
This is how I would probably approach it
List all your criteria in a column in another sheet -- let's say
Sheet2 column A - then ...
Set rngLookHere = Sheet2.Columns(1)
MatchWhat$ = Selection.Cells(x&, 8).Value
If IsError(Application.Match(MatchWhat$, rngLookHere, 0)) Then _
MsgBox "Account Name cannot be empty ! " & "Place: Row: " &
x& & " Column: 8"
Hope it helps you !
- Rodney POWELL
[ Microsoft MVP - Excel, 1997-2009 ]
www.BeyondTechnology.com
On Mar 28, 12:39 am, santosh bahuguna <santoshbahug...@gmail.com>
wrote:
> Hi
> the way you try to do is wrong ...
>
> here you go
> If Selection.Cells(x, 8) <> _
> "AD" Or Selection.Cells(x, 8) <> "AE" Or Selection.Cells(x, 8) <> "AF" Or
> Selection.Cells(x, 8) <> "AG" Or Selection.Cells(x, 8) <> "AI" Or
> Selection.Cells(x, 8) <> "AL" Or Selection.Cells(x, 8) <> "AM" Or
> Selection.Cells(x, 8) <> "AN" Or Selection.Cells(x, 8) <> "AO" _
> Or Selection.Cells(x, 8) <> "AQ" Or Selection.Cells(x, 8) <> "AR" Or
> Selection.Cells(x, 8) <> "AS" Or Selection.Cells(x, 8) <> "AT" Or
> Selection.Cells(x, 8) <> "AU" Or Selection.Cells(x, 8) <> _
> "AW" Or Selection.Cells(x, 8) <> "AX" Or Selection.Cells(x, 8) <> "AZ" Or
> Selection.Cells(x, 8) <> "BA" Or Selection.Cells(x, 8) <> "BB" Or
> Selection.Cells(x, 8) <> "BD" Or Selection.Cells(x, 8) <> "BE" Or
> Selection.Cells(x, 8) <> "BF" Or Selection.Cells(x, 8) <> "BG" _
> Or Selection.Cells(x, 8) <> "BH" Or Selection.Cells(x, 8) <> "BI" Or
> Selection.Cells(x, 8) <> "BJ" Or Selection.Cells(x, 8) <> "BM" Or
> Selection.Cells(x, 8) <> "BN" Or Selection.Cells(x, 8) <> _
> "BO" Or Selection.Cells(x, 8) <> "BR" Or Selection.Cells(x, 8) <> "BS" Or
> Selection.Cells(x, 8) <> "BT" Or Selection.Cells(x, 8) <> "BV" Or
> Selection.Cells(x, 8) <> "BW" Or Selection.Cells(x, 8) <> "BY" Or
> Selection.Cells(x, 8) <> "BZ" Or Selection.Cells(x, 8) <> "CA" _
> Or Selection.Cells(x, 8) <> "CC" Or Selection.Cells(x, 8) <> "CD" Or
> Selection.Cells(x, 8) <> "CF" Or Selection.Cells(x, 8) <> "CG" Or
> Selection.Cells(x, 8) <> "CH" Or Selection.Cells(x, 8) <> _
> "CI" Or Selection.Cells(x, 8) <> "CK" Or Selection.Cells(x, 8) <> "CL" Or
> Selection.Cells(x, 8) <> "CM" Or Selection.Cells(x, 8) <> "CN" Or
> Selection.Cells(x, 8) <> "CO" Or Selection.Cells(x, 8) <> "CR" Or
> Selection.Cells(x, 8) <> "CS" Or Selection.Cells(x, 8) <> "CU" _
> Or Selection.Cells(x, 8) <> "CV" Or Selection.Cells(x, 8) <> "CX" Or
> Selection.Cells(x, 8) <> "CY" Or Selection.Cells(x, 8) <> "CZ" Or
> Selection.Cells(x, 8) <> "DE" Or Selection.Cells(x, 8) <> _
> "DJ" Or Selection.Cells(x, 8) <> "DK" Or Selection.Cells(x, 8) <> "DM" Or
> Selection.Cells(x, 8) <> "DO" Or Selection.Cells(x, 8) <> "DZ" Or
> Selection.Cells(x, 8) <> "EC" Or Selection.Cells(x, 8) <> "EE" Or
> Selection.Cells(x, 8) <> "EG" Or Selection.Cells(x, 8) <> "EH" _
> Or Selection.Cells(x, 8) <> "ER" Or Selection.Cells(x, 8) <> "ES" Or
> Selection.Cells(x, 8) <> "ET" Or Selection.Cells(x, 8) <> "FI" Or
> Selection.Cells(x, 8) <> "FJ" Or Selection.Cells(x, 8) <> _
> "FK" Or Selection.Cells(x, 8) <> "FM" Or Selection.Cells(x, 8) <> "FO" Or
> Selection.Cells(x, 8) <> "FR" Or Selection.Cells(x, 8) <> "GA" Or
> Selection.Cells(x, 8) <> "GB" Or Selection.Cells(x, 8) <> "GD" Or
> Selection.Cells(x, 8) <> "GE" Or Selection.Cells(x, 8) <> "GF" _
> Or Selection.Cells(x, 8) <> "GG" Or Selection.Cells(x, 8) <> "GH" Or
> Selection.Cells(x, 8) <> "GI" Or Selection.Cells(x, 8) <> "GL" Or
> Selection.Cells(x, 8) <> "GM" Or Selection.Cells(x, 8) <> _
> "WS" Or Selection.Cells(x, 8) <> "YE" Or Selection.Cells(x, 8) <> "YT" Or
> Selection.Cells(x, 8) <> "ZA" Or Selection.Cells(x, 8) <> "ZM" Or
> Selection.Cells(x, 8) <> "ZW" Then
> MsgBox "Account Name cannot be empty! " & "Place: Row: " & x & "Column: 8"
> GoTo Einde
> End If
>
> use this
>
> regards
> Santosh Bahuguna
>
>
>
> On Mon, Mar 23, 2009 at 4:24 AM, xmux <kurtulu...@gmail.com> wrote:
>
> > Hi Everyone,
>
> > I am just checking a Cell with
>
> > 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 _
> > "AW" Or "AX" Or "AZ" Or "BA" Or "BB" Or "BD" Or "BE" Or "BF" Or "BG"
> > Or "BH" Or "BI" Or "BJ" Or "BM" Or "BN" Or _
> > "BO" Or "BR" Or "BS" Or "BT" Or "BV" Or "BW" Or "BY" Or "BZ" Or "CA"
> > Or "CC" Or "CD" Or "CF" Or "CG" Or "CH" Or _
> > "CI" Or "CK" Or "CL" Or "CM" Or "CN" Or "CO" Or "CR" Or "CS" Or "CU"
> > Or "CV" Or "CX" Or "CY" Or "CZ" Or "DE" Or _
> > "DJ" Or "DK" Or "DM" Or "DO" Or "DZ" Or "EC" Or "EE" Or "EG" Or "EH"
> > Or "ER" Or "ES" Or "ET" Or "FI" Or "FJ" Or _
> > "FK" Or "FM" Or "FO" Or "FR" Or "GA" Or "GB" Or "GD" Or "GE" Or "GF"
> > Or "GG" Or "GH" Or "GI" Or "GL" Or "GM" 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
>
> > If i run the Macro i got the run time Error!
> > Anyone has any idea?
>
> > Thanks- Hide quoted text -
>
> - Show quoted text -
--~--~---------~--~----~------------~-------~--~----~
-------------------------------------------------------------------------------------
Some important links for excel users:
1. Excel and VBA Tutorials(Video and Text), Free add-ins downloads at
http://www.excelitems.com
2. Excel tutorials at http://www.excel-macros.blogspot.com
3. Learn VBA Macros at http://www.vbamacros.blogspot.com
4. Excel Tips and Tricks at http://exceldailytip.blogspot.com
To post to this group, send email to excel-macros@googlegroups.com
If you find any spam message in the group, please send an email to:
Ayush Jain @ jainayus...@gmail.com or
Ashish Jain @ 26may.1...@gmail.com
-------------------------------------------------------------------------------------
-~----------~----~----~----~------~----~------~--~---
