英文:
VBA - Ignore first row in a named column
问题
如果我理解正确的话，您想要进行一个简单的检查，在检查特定命名列的值时忽略第一行（因为这些是列标题）。
我认为我可以将偏移函数与范围结合使用，但似乎不起作用：
If MyF.CountIf(Range("Deleted").Offset(1), "Deleted") > 0 Then
MsgBox ("请从列表中删除订单")
Exit Sub
End If
有人有解决方案吗？
英文:
I have a simple check that I want to do where I check a specific named column for a value but ignoring the first row (as these are the column titles).
I thought that I could combine the offset function with the range but it doesn't seem to work:
If MyF.CountIf(Range("Deleted").Offset(1), "Deleted") > 0 Then
MsgBox ("Please delete the order from the list")
Exit Sub
End If
Anyone with a solution please?
答案1
得分: 1
Assuming Range("Deleted") 是工作表中的整列，您想获取不包括第一行单元格的整列范围，那么您可以使用以下代码：
If MyF.CountIf(Range("Deleted").Resize(Range("Deleted").Parent.Rows.Count - 1, 1).Offset(1), "Deleted") > 0 Then
MsgBox "请从列表中删除该订单"
Exit Sub
End If
然而，要确定此代码是否有效，需要知道 MyF 是什么类型的对象。
如果您从整列开始，然后尝试将其向下偏移 1 行，那么您之所以出错是因为新范围包括一个不存在的单元格（例如，在现代版本的 Excel 中的第 1048577 行）。
英文:
So assuming Range("Deleted") is an entire column in a Worksheet and you want to get the Range that is the entire column less the cell in row 1, then you can use Range("Deleted").Resize(Range("Deleted").Parent.Rows.Count - 1, 1).Offset(1) which would make your code
If MyF.CountIf(Range("Deleted").Resize(Range("Deleted").Parent.Rows.Count - 1, 1).Offset(1), "Deleted") > 0 Then
MsgBox ("Please delete the order from the list")
Exit Sub
End If
... however, it's hard to know whether this is valid code without knowing what type of object MyF is.
If you are starting with an entire column and then trying to offset it down by 1 row then your error is because that new range includes a cell that doesn't exist (ie the cell on row 1048577 on modern versions of Excel)
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论