View Single Post
  #4   Report Post  
Posted to microsoft.public.excel.programming
Mike[_103_] Mike[_103_] is offline
external usenet poster
 
Posts: 9
Default Check i a value is in a Range

Armelle Aaserød wrote:

Thanks for your Input Mike!


does this reflect more what you ment to do ?

Mike



Sub SearchWeekNumber()

MyStock = 10000.355453

'ask for week number and loop until valid
WeekNum = Application.InputBox("Please enter your week number", _
, , , , , , 1)
a = Application.Match(Val(WeekNum), Range("ValidWeekNumbers"), False)

While IsError(a)

MsgBox "Number has to appear in the range
<ValidWeekNumbers" & _ vbLf & "Try again"
WeekNum = Application.InputBox("Please enter your week number", _
, , , , , , 1)
a = Application.Match(Val(WeekNum), Range("ValidWeekNumbers"), _
False)
Wend

'put the value in the found cell
If Not (IsError(a)) Then
Cells(a, 2).Value = MyStock
Cells(a, 2).NumberFormat = "#,##0.0000"
Else
MsgBox "The week has NOT been found"
End If

End Sub