0

I am trying to loop through a user input range in a sub to check if all the values are numbers.

The if statement checking if the value is numeric always executes and this code goes into an infinite loop.

A secondary problem of much lesser importance is that the error catching has an exit sub statement that is being ignored.

Sub Select_Data_Validation_Range()
On Error GoTo ErrCatcher
    Dim Stage_Range As Range
    Dim cell As Range
    'This is where the user is propmeted to input the range
    Set Stage_Range = Application.InputBox("Please Select the apporiate range for the data validation", Type:=8)
        'This loop should be looping though each cell in the selected range
        For Each cell In Stage_Range
          'If the value in the cell is not a number then an error message should be given and the sub will be called again
          If IsNumeric(cell.Value) = False Then:
               MsgBox "The Range Can contain only numbers"
               Select_Data_Validation_Range
       Next cell
    Exit Sub

ErrCatcher:
    'Err.Number 424 occurs when some clicks the x buttom on the input box
    If Err.Number = 424 Then
        MsgBox "Thanks for using the sub", , "Exit"
        'After this error I want to exit the sub but instead the sub goes back to the for loop and continues that.
        Exit Sub
    'Tryig to catch all other errors to prevent the error window from popping up
    ElseIf Err.Number <> 424 Then
        MsgBox "Exited Sub with unknown error"
        'After this error I want to exit the sub but instead the sub goes back to the for loop and continues that.
        Exit Sub
    End If
End Sub

What the user selected range should look like
enter image description here

What should happen if there is a non-numeric value in the range
enter image description here

Improve this question
5
  • so....what is the problem? Commented Jul 14, 2021 at 14:13
  • The Problem is regardless of whether or not the range contains only numeric values the if statement always executes and this code becomes an infinite loop. Will add that to the question now. Commented Jul 14, 2021 at 14:15
  • 2
    @Sam I believe there is an infinite loop because you call Select_Data_Validation_Range inside the If IsNumeric(cell.Value) = False Then statement. You never leave the master loop once you hit a non-numeric value. Commented Jul 14, 2021 at 14:17
  • I want the if statement to only execute if the value in the cell is not a number. Otherwise the the for loop should continue checking through the range. The problem (I think) is that regardless of what value is in the cell the if statement will execute and that is what is causing the infinite loop. Commented Jul 14, 2021 at 14:21
  • @Vincent is correct that you have a recursive call to validate the range. Take a look at his answer for a better procedure. Commented Jul 14, 2021 at 15:02

2 Answers 2

Reset to default
1

This doesn't do what you think it does

If IsNumeric(cell.Value) = False Then:
       MsgBox "The Range Can contain only numbers"
       Select_Data_Validation_Range

The lines following the If are not gated by the test, because of the : If you remove the : you'll get a "next without for" compile error.

Should be like this:

If IsNumeric(cell.Value) = False Then
    MsgBox "The Range Can contain only numbers"
    Select_Data_Validation_Range
End If
Improve this answer
Sign up to request clarification or add additional context in comments.

7 Comments

Yes that is exactly what is happening. I guess my question then is how do I make the lines be gated by the if statement
Yes this worked exactly as I wanted. I guess I should go back and read the documentation again. Thank you very much
@Sam By the way, you will face an issue if you ever hit a non-numeric value. You'll be calling Select_Data_Validation_Range in loop and be stuck in the same infinite loop you were trying to avoid.
Yes I am aware of that and that is why I have the error catching for when the user gives up and decides to click the x button on the input box. The reason being is that is it extremely important to other marcos in the workbook that the user selected range contains only numbers, but thanks for bringing that up
@Sam Note that best practice is to use a function (that takes the range as argument and returns true/false) rather than use a public variable. Public variables should be avoided if possible, especially in such cases where there are not required.
|
1

Try to change your code with this piece (not tested):

Dim bValidRange as Boolean
bValidRange = True
Set Stage_Range = Application.InputBox("Please Select the appropriate range for the data validation", Type:=8)
'Loop through each cell in the selected range
For Each cell In Stage_Range
    cell.Borders.ColorIndex = xlAutomatic
    If Not IsNumeric(cell.Value) Then
        bValidRange = False
        cell.Borders.ColorIndex = 3  ' Highligh borders in red
    End if
Next cell
If bValidRange Then
    MsgBox "The selected range contains only numbers."
Else
    MsgBox "Non-numeric values detected in the selected range."
End If

It will loop through the range and highligh any non-numeric cells. It will also display a message to tell the user the result of the sub.

Improve this answer

3 Comments

If you don't mind I would suggest one more improvement, to avoid popping up the error message many times (if many errors of course). Just set the highlight color and bValidRange=False while in the loop. After the loop if bValidRange is false, show the message. The user can then correct all cells with erroneous values (based on the color) and redo the test.
@TomBrunberg That's true, I'll modify my answer.
Your code is very useful as well Vincent I will probably end up using your Boolean check in my code as well.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.