using or in an if

[cereldine]

Hi, im trying to write a simple if statement such as

If Not sCode = "" or "abc" OR "xyz" Then
'' do the main piece of code here


ElseIf sCode = "abc" OR "xyz" Then

alternative piece of code.

end if

sCode is text read from a cell, the code does not like having the O
statement and displays error message 13 mismatch in data.
What is going on? im sure this was working earlier!

 

[Don Guillett]

you have to qualify each
scode<>"" or scode<>"abc" or etc

 

[cereldine]

I'm still having complications with this method. The problem being that
the code picks up "1" on the first loop but ignores "2" and "3" on the
following iterations? The exact code im using is below. My testing
brings up the value of sCode through a message box highlighted in green
so i know that the cells are being read, its just the fact that the if
statements don't recognise the fact. Ive tried using <> but this just
causes further complications. Any help grateful

Do
sCode = rng.Text

''enter the code to open up the Ons file here

If Not sCode = "1" Or sCode = "2" Or sCode = "3" Then
'' do the main piece of code here
MsgBox sCode
ElseIf sCode = "1" Or sCode = "2" Or sCode = "3" Then

Workbooks.Open (FilePath2)

Select Case sCode
there are also select case statements for "2" and "3" but the code does
not get this far so i havent included them!Case "1"
MsgBox " now you have found the case 1"

Sheets("Group 103 owning property").Select
ActiveSheet.Range("D97:W97").Select

End Select
Selection.Copy
Me.Activate

rng.Select
ActiveCell.Offset(0, 1).Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone,
SkipBlanks:= _
False, Transpose:=False
End If

Set rng = rng.Offset(1, 0)

Loop Until rng = ""

 

[Don Guillett]

Why not tell us exactly what you are trying to do along with a better
example. I would imagine that there is much easier way. Are your numbers
numbers 1 or text "1"?

 

[cereldine]

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, sheet 1 Range D4:J4
Case B : file B, sheet 2 range H3:N3
etc
end select
copy selection
reopen original worksheet
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.

 

[Andrew Taylor]

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]

thanks andrew, things are a lot clearer now

 

[cereldine]

thanks andrew, things are a lot clearer now