在 Excel Vba 中循环用户定义的范围

大佬教程收集整理的这篇文章主要介绍了在 Excel Vba 中循环用户定义的范围，大佬教程大佬觉得挺不错的，现在分享给大家，也给大家做个参考。

如何解决在 Excel Vba 中循环用户定义的范围？

您好，我正在尝试遍历子中用户输入的范围，并检查所述范围内的所有值是否都是数字。我也有一些私有错误捕获，其中有一个被忽略的退出子语句。

错误是应该检查值是否为数字的 if 语句总是执行并且此代码进入无限循环。次要问题的重要性要低得多，错误捕获不起作用。这是我的代码：

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 @R_674_8798@ge 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

what should happen if there is a non-numeric value in the range

我已经有很长一段时间没有使用堆栈溢出了，所以如果我在发布我的问题时没有遵循任何@R_659_9772@的标准，我提前道歉。如果是这种情况，请告诉我，我会解决我的问题。

解决方法

If IsNumeric(cell.value) = false Then:
       MsgBox "The Range Can contain only numbers"
       SELEct_Data_Validation_Range

If IsNumeric(cell.value) = false Then
    MsgBox "The Range Can contain only numbers"
    SELEct_Data_Validation_Range
End If

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

大佬总结

以上是大佬教程为你收集整理的在 Excel Vba 中循环用户定义的范围全部内容，希望文章能够帮你解决在 Excel Vba 中循环用户定义的范围所遇到的程序开发问题。

如果觉得大佬教程网站内容还不错，欢迎将大佬教程推荐给程序员好友。

本图文内容来源于网友网络收集整理提供，作为学习参考使用，版权属于原作者。
如您有任何意见或建议可联系处理。小编QQ：384754419，请注明来意。