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 OR statement and displays error message 13 mismatch in data.
What is going on? im sure this was working earlier!!
you have to qualify each
scode<>"" or scode<>"abc" or etc
--
Don Guillett
SalesAid Software
[email protected]
"cereldine" <[email protected]> wrote
in message news:[email protected]...
>
> 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 OR
> statement and displays error message 13 mismatch in data.
> What is going on? im sure this was working earlier!!
>
>
> --
> cereldine
> ------------------------------------------------------------------------
> cereldine's Profile:
> http://www.excelforum.com/member.php...o&userid=32069
> View this thread: http://www.excelforum.com/showthread...hreadid=530921
>
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 = ""
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"?
--
Don Guillett
SalesAid Software
[email protected]
"cereldine" <[email protected]> wrote
in message news:[email protected]...
>
> 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 = ""
>
>
> --
> cereldine
> ------------------------------------------------------------------------
> cereldine's Profile:
> http://www.excelforum.com/member.php...o&userid=32069
> View this thread: http://www.excelforum.com/showthread...hreadid=530921
>
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.
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, 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.
>
>
> --
> cereldine
> ------------------------------------------------------------------------
> cereldine's Profile: http://www.excelforum.com/member.php...o&userid=32069
> View this thread: http://www.excelforum.com/showthread...hreadid=530921
thanks andrew, things are a lot clearer now
thanks andrew, things are a lot clearer now
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks