Re: using or in an if

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

You need to be careful when combining Or and Not - sometimes
the way you would say things in English doesn't give the right
answers in VBA:. for example

"If x is not 1 or 2 then DoSomething"
should not be written as

If x <> 1 or x <> 2 Then
DoSomething
End if

because x is _always_ not equal to either 1 or 2 (or both),
so the DoSomething line will always be executed. You
need to write it as

If x <> 1 and x <> 2 Then
DoSomething
End if

Also, you can't abbreviate it as
If x <> 1 or 2 Then..

because this is parsed as (x <> 1 ) or 2, which by bitwise
evaluation is again always true.

Sometimes it's clearer if you write this sort of condition in
a "positive" way, e.g.

If x = 1 or x = 2 then
' do nothing
Else
DoSomething
End if

But again, be careful not to write

If x = 1 or 2

because this is always true, regardles of what x is.


Hope this makes sense
Adrew



cereldine wrote:
I have tested for both text e.g "a" and numbers with the same results.

In the grand scheme of things i have a workbook containing raw data, i
would like to update this raw data using a button, the people im
completing this task for would prefer to done this way rather than
having cell references (links) that may become broken. This is where
they have had problems before.

The majority of raw data (80% ) comes from the same location, the other
20% comes from various different locations. Column B contains unique
codes that make it easy to find the 80% of data from one source, for
the remaining 20% i would like to be able to say if the code is "A"
look in this location, if its "B" then look here and so on.

The plan i put together to complete this task looks something like the
following.
select cell B2 and save as variable rng
Find the value of cellB2 and put it in variable sCode
start a loop
providing sCode doesn't equal "a" or "b" or "c" etc then
Use the code to point at souce that contains 80% of data and use the
find function ive created to return relevant data.
If sCode does equal "a" 0r "b" or "c" etc then
use a Case statement to select correct source of data
Case A : file A, *** 1 Range D4:J4
Case B : file B, *** 2 range H3:N3
etc
end select
copy selection
reopen original work***
paste special in rng
Set rng = rng.Offset(1, 0)
Loop Until rng = ""


I appreciate that this might not be how you would complete the task but
im not the most gifted of programmers with most of my experience coming
from coding Access applications. would welcome any ideas or suggestions
you have tho.


--
cereldine
------------------------------------------------------------------------
cereldine's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=32069
View this thread: http://www.excelforum.com/showthread.php?threadid=530921

.

Quantcast