测试VBA中是否存在范围 [英] Test if range exists in VBA
本文介绍了测试VBA中是否存在范围的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
= OFFSET(时间!$ A $ 1,IFERROR(MATCH(DATE_RANGE_START,AllDates,0)-1,MATCH(DATE_RANGE_START,AllDates)),1,MATCH(DATE_RANGE_END,AllDates)-IFERROR(MATCH(DATE_RANGE_START ,AllDates,0)-1,MATCH(Date_Range_Start,AllDates)),4)
但是如果日期范围在表中没有数据,范围不存在(或某事,idk)。我如何在VBA中编写代码来测试这个范围是否存在?
我尝试过像
如果不是范围(DateRangeData)没有,然后
但是我得到运行时错误1004,对象_Global的方法范围失败。
解决方案
您可以复制匹配在您的VBA中使用范围之前要计数多少行,或者您可以使用错误处理:
错误恢复Next
Debug.Print范围(DateRangeData)Rows.Count
如果Err = 1004然后
MsgBoxRange Empty
Exit Sub
Else
MsgBoxRange full
End If
Err.Clear
On Error GoTo 0
I have a dynamically defined named range in my excel ss that grabs data out of a table based on a start date and an end date like this
=OFFSET(Time!$A$1,IFERROR(MATCH(Date_Range_Start,AllDates,0)-1,MATCH(Date_Range_Start,AllDates)),1,MATCH(Date_Range_End,AllDates)-IFERROR(MATCH(Date_Range_Start,AllDates,0)-1,MATCH(Date_Range_Start,AllDates)),4)
But if the date range has no data in the table, the range doesn't exists (or something, idk). How can I write code in VBA to test if this range exists or not?
I have tried something like
If Not Range("DateRangeData") Is Nothing Then
but I get "Runtime error 1004, method 'Range' of object '_Global' failed."
解决方案
You can replicate the match in your VBA to count before using the range how many rows you would have, or you can use error handling:
On Error Resume Next
Debug.Print range("DateRangeData").Rows.Count
If Err = 1004 Then
MsgBox "Range Empty"
Exit Sub
Else
MsgBox "Range full"
End If
Err.Clear
On Error GoTo 0
这篇关于测试VBA中是否存在范围的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文