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

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

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
.

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
.

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

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
.

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

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...