Prev: formula for moving information from one sheet to another sheet
Next: how do we get 2003 .xlb toolbars and custom macros to show in 07
From: Colin Hayes on 5 Jun 2010 14:44


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
From: Dave Peterson on 5 Jun 2010 15:34
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
From: מיכאל (מיקי) אבידן on 5 Jun 2010 15:49
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
> .
>
From: מיכאל (מיקי) אבידן on 5 Jun 2010 15:50
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
> .
>
From: Colin Hayes on 5 Jun 2010 15:54
In article <4C0AA6D1.C36A3F53(a)verizonXSPAM.net>, Dave Peterson
<petersod(a)verizonXSPAM.net> 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
>

 |  Next  |  Last
Pages: 1 2
Prev: formula for moving information from one sheet to another sheet
Next: how do we get 2003 .xlb toolbars and custom macros to show in 07