VBA for alternate result

[dgavin]

Is there a code or formula i can use which enter 1 or 2 alternatley each time my macro runs e.g.

when marcro runs, if worksheet 2 E20= 1 then worksheet 2 E20 = 2

when macro runs again, if worksheet 2 E20= 2 then worksheet 2 E20 = 1

when macro runs again, if worksheet 2 E20= 1 then worksheet 2 E20 = 2

and so on.

Thanks

 

[MickG]

Try this:-

With Sheets("Sheet2").Range("E20")
.Value = IIf(.Value = 1 Or .value = "", 2, 1)
End With

 

[freesurf]

Try the following (may need to tweak syntax):


first macro runs then...

iInitial_Value = Sheets("Worksheet2").Range("E20").Value
 
Select Case iInitial_Value
Case 1
Sheets("Worksheet2").Range("E20").Value = 2
Case 2
Sheets("Worksheet2").Range("E20").Value = 1
End Select

 

[dgavin]

I have changed it to this

With ws2.Range("E4")
.Value = IIf(.Value = 1 Or .value = "", 2, 1)
End With

but need a further change.....

if D34 = 1 and E4 = 1 then E4 = 2

but if D34 = 1 and E4 = 2 then E4 = 1

Thanks

 

[freesurf]

Is D34 guaranteed to =1 at all times? If not there are at least three possibilities that your if statement requires.

If you require more than three options, select case is probably the easier syntax to work with.

dM/

 

[dgavin]

D34 is 0 or 1

but i only want E4 to change when D34 = 1

 

[freesurf]

With Sheets("Worksheet2")
 
If .Range("E4").Value = 1 and .Range("D4").Value = 1  then 
    .Range("E4").Value = 2
Elseif .Range("E4").Value = 2 and .Range("D4").Value = 1  then

 

[freesurf]

sorry pulled the trigger too quickly!

With Sheets("Worksheet2")

If .Range("E4").Value = 1 and .Range("D4").Value = 1 then 
       .Range("E4").Value = 2
Elseif .Range("E4").Value = 2 and .Range("D4").Value = 1 then 
       .Range("E4").Value = 1
Else

 

[freesurf]

sorry pulled the trigger too quickly again! I'm sed to indenting with a tab!
again, you may need to play with this, e.g. exit if may be one word, but VBE should help here.

With Sheets("Worksheet2")
 If .Range("E4").Value = 1 and .Range("D4").Value = 1 then 
  .Range("E4").Value = 2
 Elseif .Range("E4").Value = 2 and .Range("D4").Value = 1 then 
  .Range("E4").Value = 1
 Else 
  Exit if
 End if
End With