检查值是否在范围内而不循环 [英] Check if value exists in range without looping

问题描述

我习惯于在python语法中检查 list1 中是否存在 7 ，您只需在list1 中键入 7并返回一个布尔值.如何在vba中执行类似的操作?

I'm used to python syntax where to check if 7 is in list1 you simply type 7 in list1 and it returns a boolean. How can I perform something like this in vba?

我目前正在大范围巡视.我想偶尔检查一下我要遍历的值是否在不同的范围内.如果我必须在循环中嵌套更多的循环，则速度可能会慢得多.解决此问题的最快方法是什么?

I'm currently looping through a large range. I want to occasionally check if a value i'm looping over is in a different range. This could get much slower if I had to nest more loops into my loops. What's the fastest way to approach this problem?

For i = 400 To 1 Step -1:
'doing other things

'here's some psuedo-code of what I want to do
If Sheets("Sheet2").Cells(i, 2).Value In Sheets("Sheet1").Range("NamedRange")
Sheets("Sheet2").Cells(i, 2).EntireRow.Delete
End If

Next i

推荐答案

使用一个countif，如果它大于零，则说明它存在于列表中:

Use a countif, if it's greater than zero then you know it exists in the list:

If Application.WorksheetFunction.CountIf(Sheets("Sheet1").Range("NamedRange"), Sheets("Sheet2").Cells(i, 2).Value) > 0 Then
    Sheets("Sheet2").Cells(i, 2).EntireRow.Delete
End If

这篇关于检查值是否在范围内而不循环的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！