How to Loop macro field until you receive a number ?

[vhar]

Hi everyone,

I am learning macros through Chandoo's 5-part series. In his part-2 series (http://chandoo.org/wp/2011/08/30/variables-conditions-loops-in-vba/), in the example provided, I tried to make some changes and learn.

1. When it ask for input for the "Sales for the Stoe", it accepts any thing even a string.
2. Tried to modify the code to accept only numbers.
-------------------------------------------------------------------------

Sub captureSales()
    'when you run this macro, it will take the sales of all the 24 stores we own
    'it will ask for a reason if the sales are too low or too high
  
    Dim storeSales As Long
    Dim storeNum As Integer
    Dim reason As String
    Dim store As Range
  
    storeNum = 1
    For Each store In Range("C7:C30")
        store.Value = InputBox("Sales for Store " & storeNum)
            If Not IsNumeric(store.Value) Then
            MsgBox "Only numbers are allowed!"
            store.Value = vbNullString
            End If
        If store.Value < 500 Or store.Value > 5000 Then
            reason = InputBox("Why are the sales deviated?", "Reason for Deviation", "Reason for Deviation")
            store.Offset(, 1).Value = reason
        End If
        storeNum = storeNum + 1
    Next store
End Sub

-------------------------------------------------------------------------
3. It works ok and moves to next field if I input any 'Text' but I want the task to repeat until it accepts the number for that pertcular store. How I can I do that.

Thanks in advance..

Here is the original file...
http://img.chandoo.org/vba/crash-course/variables-conditions-and-loops.xls

 

[vletm]

Did You mean something like this?
Add those two lines ( 'Do' and 'Loop ...' to You code ...

Do
  store.Value = InputBox("Sales for Store " & storeNum)
  If Not IsNumeric(store.Value) Then
    MsgBox "Only numbers are allowed!" store.Value = vbNullString
  End If
Loop Until IsNumeric(store.value)

 

[vhar]

Hi,

It didn't work.

Suppose if I enter some test "xyz" for store number -5, it should repeatedly ask until it gets the number. But with the code above, it wont take the text but i bypasses to the next store-number.

 

[vletm]

Sorry .. ( 'and store.Value <> Empty' missed from code )

Loop Until IsNumeric(store.Value) And store.Value <> Empty

 

[vhar]

Sorry..Neither this works and going to 'Non-responsive mode'.
Can you try once at your side..

 

[vletm]

What do You mean with 'Non-responsive mode'?
If You write number, it will ask next question and after that continues asking next number.
If You write non-number, it will give a Message and asks again number as long time as You have written number.
The 'Cancel-button' don't work now! You have to give 24 numbers.
You wanted that this accepts only numbers!
If You want to use the Cancel-button ... it's possible to do too.

 

[Marc L]

Hi !

vhar, according to forum rules, use code tags ‼

Try this (Cancel button or empty answer end procedure) :​

Sub captureSales()
    Const LF2 = vbLf & vbLf
    Dim store As Range, storeNum As String, OK As Boolean

    For Each store In Range("C7:C30")
        storeNum = "  Sales for Store #" & store.Offset(, -1).Value2

        Do
             V = InputBox(LF2 & [C6].Text & " :", storeNum)
            OK = IsNumeric(V)

            If OK Then
                store.Value2 = V

                If V < 500 Or V > 5000 Then
                    V = InputBox(LF2 & [D6].Text & " :", storeNum)
                    If V = "" Then Exit Sub
                    store.Offset(, 1).Value2 = V
                End If

            ElseIf V = "" Then
                Exit Sub
            End If
        Loop Until OK
    Next store
End Sub

Do you like it ? So thanks to click on bottom right Like !​