Hi,

I have a worksheet on which I have some week numbers entered in Column A.
Within a Sub I use an InputBox to get the user to type in a week number.
What I would like to do is to check whether the week number typed in by the
user is in Column A. If it isn't I would like to ask the user to enter a
valid week number and show the InputBox again. If the week number entered is
in Column A, then I read a value from a Named Range and put in in Column B
beside the week number entered

What I did was a simple Sub but I have no validation so it's a bit poor :

Weeknum = InputBox("Please enter your week number")

Do
i = i + 1

Loop Until Range("A" & i).Value = Weeknum


Range("B" & i).Value = MyStock
Range("B" & i).NumberFormat = "#,##0.0000"

I tried writing something with Cells.Find but it didn't work I got a type
mismatch error.
Can someone help me with this ?

Thanks in advance,

Armelle Aaserød

Armelle Aaserød wrote:
Hi,

Hello Armelle,
try something like this

Sub SearchWeekNumber()
MyStock = 10000.355453
WeekNum = Application.InputBox("Please enter your week number", _
, , , , , , 1)
While WeekNum < 1 Or WeekNum 52
MsgBox "Number has to be between 1 and 52" & vbLf & "Try again"
WeekNum = Application.InputBox("Please enter your week number", _
, , , , , , 1)
Wend

a = Application.Match(Val(WeekNum), Columns(1), False)
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


Mike,
Luxembourg

Thanks for your Input Mike!
However the week number list I have is a list of 18 week numbers which do
not follow a logical order so that's why I named the range containing all
week numbers and what I really wanted to do was to check that the week
number entered by the user was contained in the Named Range.
If you see what I mean...

Armelle




"Mike" a écrit dans le message de
...
Armelle Aaserød wrote:
Hi,

Hello Armelle,
try something like this

Sub SearchWeekNumber()
MyStock = 10000.355453
WeekNum = Application.InputBox("Please enter your week number", _
, , , , , , 1)
While WeekNum < 1 Or WeekNum 52
MsgBox "Number has to be between 1 and 52" & vbLf & "Try again"
WeekNum = Application.InputBox("Please enter your week number", _
, , , , , , 1)
Wend

a = Application.Match(Val(WeekNum), Columns(1), False)
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


Mike,
Luxembourg

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

Mike,

this is exactly what I was looking for ! Thanks again for your help.

Have a nice week-end

Armelle


"Mike" a écrit dans le message de
...
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