大佬教程收集整理的这篇文章主要介绍了在 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 message should be given and the sub will be called again
If IsNumeric(cell.@R_696_7548@ = 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_345_9772@的标准,我提前道歉。如果是这种情况,请告诉我,我会解决我的问题。
这和你想的不一样
If IsNumeric(cell.@R_696_7548@ = false Then:
MsgBox "The Range Can contain only numbers"
SELEct_Data_Validation_Range
If
后面的行没有被测试门控,因为 :
如果删除 :
,您将收到“next without for”编译错误。
应该@R_616_9811@:
If IsNumeric(cell.@R_696_7548@ = 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.@R_696_7548@ 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,请注明来意。