macro

[neilb514]

Hi
I have the below macro which works fine, but I need to add to it.

Sub Button6_Click()
Range("c12") = Range("c12") + Range("d8")


Range("A8,b8,b7,c8") = 0



End Sub

In cell c14 I want to display a number when I push the above button. In cell
f2 I have a number that changes when data is added to my worksheet. if the
number in f2 is +1 or less i need a "1" displayed in c14, if it is +2 or + 3
I need a "2", if it is +4 or +5 I need a "3", +6 or +7 I need a "4", +8 or
more i need a "5". any help will be appretiated.

 

[Mike H]

one way:-

Sub Button1_Click()
Range("c12") = Range("c12") + Range("d8")
Range("A8,b8,b7,c8") = 0

If IsNumeric(Range("F2").Value) Then
Select Case Range("F2").Value
Case Is <= 1
myvalue = 1
Case Is <= 3
myvalue = 2
Case Is <= 5
myvalue = 3
Case Is <= 7
myvalue = 4
Case Else
myvalue = 5
End Select
Range("C14").Value = myvalue
End If

End Sub


Mike

 

[Don Guillett]

This could be put into a worksheet_change event to be completely automatic

Sub ifnum()
num = range("f2")
If num < 1 Then
nn = 1
Else
nn = Application.Min(Int(num / 2) + 1, 5)
End If
range("c14")=nn
End Sub