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. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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 | |
|
|