VBA check for value in a range

Question

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":

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

You can use [AUTOFILTER](http://stackoverflow.com/questions/11631363/how-to-copy-a-line-in-excel-using-a-specific-word-and-pasting-to-another-excel-s) to achieve what you want... — Siddharth Rout, Sep 09 '14 at 19:28

score 4 · Accepted Answer · answered Sep 09 '14 at 20:09

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.

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

Thanks a bunch! Yes a case statement is better for what I'm doing, since it's multiple values that I'm checking. "rngSource" is not used anymore, in your revision, so I'm taking it out. Now I just have to figure out what I want it to do when in each check. — Chris, Sep 10 '14 at 14:24

score 1 · Answer 2 · answered Jun 22 '18 at 20:57

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"

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

score 0 · Answer 3 · answered Oct 07 '16 at 11:51

Pass value to find and Column where value need to be checked. It will return row num if its found else return 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 & "$1:$" & 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

score 0 · Answer 4 · answered May 25 '19 at 01:06

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

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:

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)

Hope this helps.

score -1 · Answer 5 · answered Sep 09 '14 at 19:38

-1

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

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

answered Sep 09 '14 at 19:38

Matt Cremeens

4,951
7
38
67

VBA check for value in a range

5 Answers5

Linked