VBA 检查范围内的值

VBA check for value in a range

提问by Chris

I am trying to loop through a column and if cells = "what i'm lookng for" then do something. I have this so far, where I'm off is in the if statement where I check for the "name":

我正在尝试遍历一列，如果单元格 =“我正在寻找的内容”，则执行某些操作。到目前为止，我有这个，我在 if 语句中检查“名称”：

Option Explicit

Sub test()

Dim wksDest             As Worksheet
Dim wksSource           As Worksheet

Dim rngSource           As Range

Dim name                 As String

Dim LastRow             As Long
Dim LastCol             As Long
Dim c                   As Long

Application.ScreenUpdating = False

Set wksSource = Worksheets("Sheet1")

With wksSource
LastCol = .Cells(1, .Columns.Count).End(xlToLeft).Column
For c = 16 To 20
LastRow = .Cells(.Rows.Count, c).End(xlUp).Row
Set rngSource = .Range(.Cells(5, 16), .Cells(LastRow, 16))
name = rngSource.Value

If name = "mark"
do something 
End If

Next c
End With

Application.ScreenUpdating = True

'MsgBox "Done!", vbExclamation

End Sub

回答by barryleajo

OK Chris Maybe a bit of simplification is required but also a few assumptions. It doesn't seem like LastCol is being used for anything - so let's assume this is the Column you want to loop through. Your loop has fixed start and end values yet you are determining the LastRow - so let's assume you want to start from row 5 (in your code) and loop to the LastRow in the LastCol. In order to determine LastCol you must have data in the row you are using to do this - so let's assume that there are values in row 1 in all columns up to column you want to loop say 16 (in your code). If you want to (IF) test for a single (string) value in this case then you must arrange for your rngSource to be a single cell value. You also don't need to assign this to a variable unless you need to use it again. Finally, if you want to check for other values you may want to consider using a SELECT CASE structure in place of your IF THEN structure. Have a look at the following and change my assumptions to meet your requirement - good luck.

好的 Chris 也许需要一些简化，但也需要一些假设。LastCol 似乎没有被用于任何事情 - 所以让我们假设这是你想要循环的列。您的循环具有固定的开始和结束值，但您正在确定 LastRow - 因此假设您想从第 5 行（在您的代码中）开始并循环到 LastCol 中的 LastRow。为了确定 LastCol，您必须在用于执行此操作的行中拥有数据 - 因此，让我们假设所有列中的第 1 行中都有值，直到您要循环的列中，例如 16（在您的代码中）。如果您想 (IF) 在这种情况下测试单个（字符串）值，那么您必须将 rngSource 安排为单个单元格值。您也不需要将此分配给变量，除非您需要再次使用它。最后，如果您想检查其他值，您可能需要考虑使用 SELECT CASE 结构代替 IF THEN 结构。查看以下内容并更改我的假设以满足您的要求 - 祝您好运。

Sub test()

Dim wksDest             As Worksheet
Dim wksSource           As Worksheet

Dim rngSource           As Range

Dim name                 As String

Dim LastRow             As Long
Dim LastCol             As Long
Dim c                   As Long

Application.ScreenUpdating = False

Set wksSource = Worksheets("Sheet1")

    With wksSource
        LastCol = .Cells(1, .Columns.Count).End(xlToLeft).Column
        LastRow = .Cells(Rows.Count, LastCol).End(xlUp).Row

        FirstRow = 5
            For c = FirstRow To LastRow
                If .Range(.Cells(c, LastCol), .Cells(c, LastCol)).Value = "Mark" Then
                    MsgBox ("do something")
                End If
            Next c
    End With

End Sub

回答by Matt Cremeens

I'm guessing what you really want to do is loop through your range rngSource. So try

我猜你真正想做的是循环遍历你的 range rngSource。所以试试

Set rngSource = .Range(.Cells(5, 16), .Cells(LastRow, 16))
for myCell in rngSource
  if myCell.Value = "mark" then
    do something
  end if
next myCell

回答by KannanRG

Pass value to find and Column where value need to be checked. It will return row num if its found else return 0.

将值传递给需要检查值的查找和列。如果找到它，它将返回行号，否则返回 0。

Function checkForValue(FindString As String,ColumnToCheck as String) As Long
        SheetLastRow = Sheets("Sheet1").Cells.Find(What:="*", SearchOrder:=xlRows, SearchDirection:=xlPrevious, LookIn:=xlValues).row
         With Sheets("Sheet1").Range("$" & ColumnToCheck & ":$" & ColumnToCheck & "$" & CStr(SheetLastRow) ) 
                Set rng = .Find(What:=FindString, _
                                After:=.Cells(.Cells.Count), _
                                LookIn:=xlValues, _
                                lookat:=xlWhole, _
                                SearchOrder:=xlByRows, _
                                SearchDirection:=xlNext, _
                                MatchCase:=False)
                If Not rng Is Nothing Then
                    checkForValue = rng.row 'return row its found
                    'write code you want.                   
                Else
                    checkForValue = 0
                End If
            End With
End Function

回答by Hari Das

You can just do that with one line.

你可以用一行来做到这一点。

If Not IsError(Application.Match(ValueToSearchFor, RangeToSearchIn, 0)) Then
'The value found in the given range
End If

Example:Search for "Canada" in column C of sheet named "Country"

示例：在名为“Country”的表格的 C 列中搜索“Canada”

If Not IsError(Application.Match("Canada", Sheets("Country").Range("C:C"), 0)) Then
   'The value found in the given range
End If

回答by Javier Arias

I tried Hari's suggestion, but Application.Match works weird on range names (not recognizing them...)

我尝试了 Hari 的建议，但 Application.Match 在范围名称上的工作很奇怪（无法识别它们......）

Changed to: WorksheetFunction.Match(... It works, but when value is not present A runtime ERROR jumps before IsError(...) is evaluated. So I had to write a simple -no looping- solution:

更改为： WorksheetFunction.Match(... 它可以工作，但是当值不存在时在评估 IsError(...) 之前运行时 ERROR 跳转。所以我不得不编写一个简单的 - 无循环 - 解决方案：

dim Index as Long

Index = -1
On Error Resume Next
  Index = WorksheetFunction.Match(Target,Range("Edificios"), 0) 'look for Target value in range named: Edificios
On Error GoTo 0
If Index > 0 Then
    ' code for existing value found in Range @ Index row
End If

Remeber Excel functions first index = 1 (no zero based)

记住 Excel 函数第一个索引 = 1（不基于零）

Hope this helps.

希望这可以帮助。