VBA code with and / or statement

  • Thread starter Thread starter Steve
  • Start date Start date
S

Steve

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
 
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.
 
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
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
Click to expand...
 
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
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]
Click to expand...
Thanks for any help you can provide
Click to expand...
Click to expand...

Thanks for the help ! Steve
 
Back
Top