Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
DiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWord
DirectoryUser Groups
Related Topics
Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ...

MS Office Forum / Excel / Programming / January 2008

Tip: Looking for answers? Try searching our database.

VBA code with and / or statement

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
Steve - 31 Jan 2008 15:39 GMT
I cannot get this to run the [ELK] macro when these 3 conditions are
met.  It worked until I added the OR statement.   I would really like
it to check if the reference cells have numbers in them. Then if NOT
run the ELK macro.       If Sheet1.Range("b10").Value <> "" Or
Sheet1.Range("b19").Value <> "" And Sheet1.Range("b11:aa11").Value =
"" Then [ELK]

Thanks for any help you can provide
Nigel - 31 Jan 2008 16:11 GMT
Think of each logical test returning a TRUE or FALSE

.....("b10") <> ""  is TRUE if NOT equal empty string
.....("b19") <> "" is TRUE if NOT equal empty string

If you want BOTH to be TRUE then you must AND them, otherwise OR will return
TRUE if either is TRUE.

Finally the last test ...("b11:aa11") is TRUE if the values are = empty
strings; since you want this condition to be TRUE and the previous condition
to also be TRUE you should AND these as well.

Signature

Regards,
Nigel
nigelnospam@9sw.co.uk

>I cannot get this to run the [ELK] macro when these 3 conditions are
> met.  It worked until I added the OR statement.   I would really like
[quoted text clipped - 4 lines]
>
> Thanks for any help you can provide
Dave Peterson - 31 Jan 2008 18:40 GMT
If you want to check a multicell range to see if it's empty (no formulas, no
values), you could use:

if application.counta(sheet1.range("b11:aa11")) = 0 then
 'all empty

if you wanted to check to see if they were all filled:

if application.counta(sheet1.range("b11:aa11")) _
 = sheet1.range("b11:aa11").cells.count then
 'all filled

If you wanted to check to see if at least one cell had something (a value or a
formula) in it:

if application.counta(sheet1.range("b11:aa11")) > 0 then
 'all empty

And if I were you, I'd use some ()'s in my check:

if sheet1.range("b10").value <> "" _
or sheet1.range("b19").value <> "" _
And application.counta(sheet1.range("b11:a11")) <> 0 then

if (sheet1.range("b10").value <> "" _
 or sheet1.range("b19").value <> "") _
And application.counta(sheet1.range("b11:a11")) <> 0 then

or

if sheet1.range("b10").value <> "" _
or (sheet1.range("b19").value <> "" _
And application.counta(sheet1.range("b11:a11")) <> 0) then

Your code may work exactly the way you want without the ()'s, but personally,
and/or's without ()'s scare the heck out of me.

> I cannot get this to run the [ELK] macro when these 3 conditions are
> met.  It worked until I added the OR statement.   I would really like
[quoted text clipped - 4 lines]
>
> Thanks for any help you can provide

Signature

Dave Peterson

Steve - 31 Jan 2008 23:47 GMT
> If you want to check a multicell range to see if it's empty (no formulas, no
> values), you could use:
[quoted text clipped - 45 lines]
>
> Dave Peterson

Thanks for the help !   Steve
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.