VBA Repeat InputBox Entry IF...

[myactiondesign]

Hi All,

I'm really stuck and would appreciate some help if any of you have five minutes to spare - my VBA knowledge is growing, but still limited.

I am looking to have a user InputBox to enter sales for 6 weeks, as shown below:

---
Sub InputUserData()
'This sub will create six messages boxes for user input
'The user input will be the first six weeks' sales of a new product
'The data will automatically be input into "Data"


Worksheets("Data").Activate


Dim intWeeknum As Integer
Dim rngWeeks As Range


intWeeknum = 1


For Each rngWeeks In Range("C8:C13")
rngWeeks.Value = InputBox("Please enter the sales for week " & intWeeknum)
intWeeknum = intWeeknum + 1
Next rngWeeks


End Sub
---

The issue: I'm looking for each InputBox to check that a) the value is between two certain values [say $1'000 and $2'000], and b) that the value is an integer.

If either of these conditions are not met, I need to repeat the same week's input.

I have literally no idea how to do this within an existing For statement.

Any ideas?

Any and all help will be greatly appreciated!

Thanks in advance!

!&...

 

[Andrew Poulsom]

How would you do it if you didn't have a For Each ... Next loop?

 

[myactiondesign]

How would you do it if you didn't have a For Each ... Next loop?

Hi Andrew,

Thanks for getting back to me.

Would I simply have a section of the code for each InputBox individually and create if statements within them?

Eg.

Dim saleswk1 As Integer
Dim saleswk2 As Integer
Dim saleswk3 As Integer
... so on

saleswk1.value = InputBox("What were the sales for week 1?")

Select Case
Case saleswk1 < 1000 Or > 2000 Then
MsgBox ("Are you sure this is correct?", vbYesNoCancel)
If vbNo Then

saleswk1.value = InputBox("What were the sales for week 1?")

End If

Case saleswk2 ...

---

Would this be the way to go about it?

 

[Andrew Poulsom]

Try:

Sub InputUserData()
'   This sub will create six messages boxes for user input
'   The user input will be the first six weeks' sales of a new product
'   The data will automatically be input into "Data"
    Dim intWeeknum As Integer
    Dim rngWeeks As Range
    Dim Sales As Variant
    intWeeknum = 1
    For Each rngWeeks In Worksheets("Data").Range("C8:C13")
        Do
            Sales = InputBox("Please enter the sales for week " & intWeeknum)
            Debug.Print Sales
            If IsNumeric(Sales) And Sales <> "" And Sales > 1000 And Sales < 2000 Then
                rngWeeks.Value = Sales
                intWeeknum = intWeeknum + 1
                Exit Do
            Else
                MsgBox "Please enter a value between 1000 and 2000"
            End If
        Loop
    Next rngWeeks
End Sub

Note that the user won't be able to Cancel the InputBox.

 

[myactiondesign]

Thank you so much Andrew! That works absolutely perfectly!

And now a lot of other doors have opened as well, for other parts of the code I was struggling with or having to write really long macros.

I appreciate your help very much.

Have a fantastic Christmas and a great New Year!

!&...