A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Excel » General Discussion
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Nested AND OR expressions



 
 
Thread Tools Display Modes
  #1  
Old June 5th, 2010, 07:44 PM posted to microsoft.public.excel.misc
Colin Hayes
external usenet poster
 
Posts: 313
Default Nested AND OR expressions



HI

I need to express something in VBA , and am having trouble with it.

I need to say this :

IF E1 CONTAINS "1bx" OR "2bx" OR "3bx" AND J1 =11 THEN PUT 1 , OTHERWISE
PUT J1

Can someone assist with some code to make this happen , please?

Grateful for any help.



Best Wishes
  #2  
Old June 5th, 2010, 08:34 PM posted to microsoft.public.excel.misc
Dave Peterson
external usenet poster
 
Posts: 19,791
Default Nested AND OR expressions

With worksheets("Someworksheetnamehere")
if .range("J11").value = 11 _
and (lcase(.range("e1").value) = "1bx" _
or lcase(.range("e1").value) = "2bx" _
or lcase(.range("e1").value) = "3bx") then
.range("iv99").value = 1
else
.range("iv99").value = .range("j1").value
end if
end with

I "put" the value in IV99, I'm not sure where you wanted it.




Colin Hayes wrote:

HI

I need to express something in VBA , and am having trouble with it.

I need to say this :

IF E1 CONTAINS "1bx" OR "2bx" OR "3bx" AND J1 =11 THEN PUT 1 , OTHERWISE
PUT J1

Can someone assist with some code to make this happen , please?

Grateful for any help.

Best Wishes


--

Dave Peterson
  #3  
Old June 5th, 2010, 08:49 PM posted to microsoft.public.excel.misc
מיכאל (מיקי) אבידן
external usenet poster
 
Posts: 562
Default Nested AND OR expressions

Try:
------------------
Sub Test()
If [J1] = 11 And [E1] = "1bx" Or [E1] = "2bx" Or [E1] = "3bx" Then
Result = 1
Else
Result = [J1]
End If
End Sub
--------------
Micky


"Colin Hayes" wrote:



HI

I need to express something in VBA , and am having trouble with it.

I need to say this :

IF E1 CONTAINS "1bx" OR "2bx" OR "3bx" AND J1 =11 THEN PUT 1 , OTHERWISE
PUT J1

Can someone assist with some code to make this happen , please?

Grateful for any help.



Best Wishes
.

  #4  
Old June 5th, 2010, 08:50 PM posted to microsoft.public.excel.misc
מיכאל (מיקי) אבידן
external usenet poster
 
Posts: 562
Default Nested AND OR expressions

Or maybe this:
---------------------
Sub Test()
If [J1] = 11 Then
If [E1] = "1bx" Or [E1] = "2bx" Or [E1] = "3bx" Then
Result = 1
Else
Result = [J1]
End If
End If
End Sub
-------------
Micky


"Colin Hayes" wrote:



HI

I need to express something in VBA , and am having trouble with it.

I need to say this :

IF E1 CONTAINS "1bx" OR "2bx" OR "3bx" AND J1 =11 THEN PUT 1 , OTHERWISE
PUT J1

Can someone assist with some code to make this happen , please?

Grateful for any help.



Best Wishes
.

  #5  
Old June 5th, 2010, 08:54 PM posted to microsoft.public.excel.misc
Colin Hayes
external usenet poster
 
Posts: 313
Default Nested AND OR expressions

In article , Dave Peterson
writes

With worksheets("Someworksheetnamehere")
if .range("J11").value = 11 _
and (lcase(.range("e1").value) = "1bx" _
or lcase(.range("e1").value) = "2bx" _
or lcase(.range("e1").value) = "3bx") then
.range("iv99").value = 1
else
.range("iv99").value = .range("j1").value
end if
end with

I "put" the value in IV99, I'm not sure where you wanted it.


Hi Dave

OK Thanks for that.

An extra query - how would this be expressed as a formula in a helper
column?

I assume then also I could drag it down to apply to other rows too.

The reason I'm asking is that I may need to use it that way after all.

Thanks again for your help.



Best Wishes






Colin Hayes wrote:

HI

I need to express something in VBA , and am having trouble with it.

I need to say this :

IF E1 CONTAINS "1bx" OR "2bx" OR "3bx" AND J1 =11 THEN PUT 1 ,

OTHERWISE
PUT J1

Can someone assist with some code to make this happen , please?

Grateful for any help.

Best Wishes



  #6  
Old June 5th, 2010, 09:02 PM posted to microsoft.public.excel.misc
מיכאל (מיקי) אבידן
external usenet poster
 
Posts: 562
Default Nested AND OR expressions

It must be the fatigue:
---------------------
Sub Test()
If [J1] = 11 And ([E1] = "1bx" Or [E1] = "2bx" Or [E1] = "3bx") Then
Result = 1
Else
Result = [J1]
End If
End Sub
-------------
Micky


"Colin Hayes" wrote:



HI

I need to express something in VBA , and am having trouble with it.

I need to say this :

IF E1 CONTAINS "1bx" OR "2bx" OR "3bx" AND J1 =11 THEN PUT 1 , OTHERWISE
PUT J1

Can someone assist with some code to make this happen , please?

Grateful for any help.



Best Wishes
.

  #7  
Old June 6th, 2010, 03:10 AM posted to microsoft.public.excel.misc
T. Valko
external usenet poster
 
Posts: 15,759
Default Nested AND OR expressions

An extra query - how would this be expressed
as a formula in a helper column?


See your other post

--
Biff
Microsoft Excel MVP


"Colin Hayes" wrote in message
...
In article , Dave Peterson
writes

With worksheets("Someworksheetnamehere")
if .range("J11").value = 11 _
and (lcase(.range("e1").value) = "1bx" _
or lcase(.range("e1").value) = "2bx" _
or lcase(.range("e1").value) = "3bx") then
.range("iv99").value = 1
else
.range("iv99").value = .range("j1").value
end if
end with

I "put" the value in IV99, I'm not sure where you wanted it.


Hi Dave

OK Thanks for that.

An extra query - how would this be expressed as a formula in a helper
column?

I assume then also I could drag it down to apply to other rows too.

The reason I'm asking is that I may need to use it that way after all.

Thanks again for your help.



Best Wishes






Colin Hayes wrote:

HI

I need to express something in VBA , and am having trouble with it.

I need to say this :

IF E1 CONTAINS "1bx" OR "2bx" OR "3bx" AND J1 =11 THEN PUT 1 ,

OTHERWISE
PUT J1

Can someone assist with some code to make this happen , please?

Grateful for any help.

Best Wishes





  #8  
Old June 6th, 2010, 12:05 PM posted to microsoft.public.excel.misc
Stan Brown
external usenet poster
 
Posts: 536
Default Nested AND OR expressions

On Sat, 5 Jun 2010 19:44:24 +0100, Colin Hayes wrote:

HI

I need to express something in VBA , and am having trouble with it.

I need to say this :

IF E1 CONTAINS "1bx" OR "2bx" OR "3bx" AND J1 =11 THEN PUT 1 , OTHERWISE
PUT J1

Can someone assist with some code to make this happen , please?

Grateful for any help.


The most straightforward way, I think, is

=IF( AND( OR(E1="1bx",E1="2bx","E1="3bx"), J1=11), 1, J1)


--
Stan Brown, Oak Road Systems, Tompkins County, New York, USA
http://OakRoadSystems.com
Shikata ga nai...
 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 09:50 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright 2004-2024 OfficeFrustration.
The comments are property of their posters.