if cell value is greater than or equal to 2 change its value to 1

[intothewild]

how do you write this in vba:

if cell value is greater than or equal to 2 change its value to 1

[Jakobshavn]

Sub ChangeIt()
With Selection
    If .Value > 2 Then .Value = 1
End With
End Sub

[intothewild]

what about a worksheet function?

[Jakobshavn]

With a worksheet function you would be putting 1 in a separate cell.

if A1 has the value, then in B1:

=IF(A1>=2,1,A1)

[intothewild]

With a worksheet function you would be putting 1 in a separate cell.

if A1 has the value, then in B1:

=IF(A1>=2,1,A1)

Thanks, seperate col is not a problem.


When i tried it in VBA it didnt work out for me...can you see what ive done wrong? ...

Workbooks("dataset1.xls").Sheets("Sheet3").Activate
Range("F2:F11").Select

With Selection
    If .Value > 2 Then .Value = 1
End With

End Sub

doesnt like the range i think.

[Jakobshavn]

We need to loop over the cells in the range:

Sub Demo()
For Each cel In Workbooks("dataset1.xls").Sheets("Sheet3").Range("F2:F11")
    With cel
        If .Value > 2 Then .Value = 1
    End With
Next cel
End Sub

[intothewild]

brilliant thanks Jake!