Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Find row number of matching value

Tags:

excel

vba

I've been trying several different methods of finding the row number of bingo (listed and separated by asterisks) but none seem to work. What am I doing wrong? In all instances I've tried looking both for Bingo and "Bingo".

Sub Find_Bingo()

Dim wb As Workbook
Dim ws As Worksheet
Dim FoundCell As Range
Set wb = ActiveWorkbook
Set ws = ActiveSheet

    Const WHAT_TO_FIND As String = "Bingo"

    Set FoundCell = ws.Range("A").Find(What:=WHAT_TO_FIND)
    If Not FoundCell Is Nothing Then
        MsgBox (WHAT_TO_FIND & " found in row: " & FoundCell.Row)
    Else
        MsgBox (WHAT_TO_FIND & " not found")
    End If

'************

    With Sheet1
        Set FoundCell = Cells.Find(What:=Bingo, After:=.Cells(1, 1), _
 LookIn:=xlValues, lookat:= xlPart, SearchOrder:=xlByRows, _ 
 SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False).Activate
    End With

'************

Set FoundCell = Sheets("Sheet1").Columns("E").Find(Bingo, _
ActiveSheet.Cells(2, 2), LookIn:=xlValue, lookat:=xlWhole)

'************

FoundCell = Range("A:M").Find(Bingo)

'************

FoundCell = Application.WorksheetFunction.Match(Bingo, Range("A1:A200"), 0)

'************

FoundCell = Worksheets("Sheet1").Columns(1).Find(Bingo).Row

'************

Range("A:A").Find(Bingo, Range("A1")).Row

'************

ActiveWorkbook.Worksheets("Sheet1").Columns(1).Find(Bingo).Select

'************
End Sub
like image 958
Cats and Dogs Avatar asked Aug 24 '15 19:08

Cats and Dogs


People also ask

How do I find the row number in a match in Excel?

=INDEX() returns the value of a cell in a table based on the column and row number. =MATCH() returns the position of a cell in a row or column. Combined, the two formulas can look up and return the value of a cell in a table based on vertical and horizontal criteria.

How do I find a row number based on value?

The ROW function returns the row number for a cell or range. For example, =ROW(C3) returns 3, since C3 is the third row in the spreadsheet. When no reference is provided, ROW returns the row number of the cell which contains the formula.

How do I get the entire row of a matched value?

To get the whole row data of a matched value, please apply the following formula: Enter this formula: =VLOOKUP($F$2,$A$1:$D$12,COLUMN(A1),FALSE) into a blank cell where you want to get the result, for instance, H1, and then drag the formula to right ( from H2 to K2), and you will get the whole row data you want.

How do you find the row number in a formula?

The method to use this function is as follows: =ROW( Value ). It will only show the cell's row number, not its value. For example, =ROW(A5) returns 5 because A5 is the fifth row in the Excel spreadsheet.


1 Answers

For your first method change ws.Range("A") to ws.Range("A:A") which will search the entirety of column a, like so:

Sub Find_Bingo()

        Dim wb As Workbook
        Dim ws As Worksheet
        Dim FoundCell As Range
        Set wb = ActiveWorkbook
        Set ws = ActiveSheet

            Const WHAT_TO_FIND As String = "Bingo"

            Set FoundCell = ws.Range("A:A").Find(What:=WHAT_TO_FIND)
            If Not FoundCell Is Nothing Then
                MsgBox (WHAT_TO_FIND & " found in row: " & FoundCell.Row)
            Else
                MsgBox (WHAT_TO_FIND & " not found")
            End If
End Sub

For your second method, you are using Bingo as a variable instead of a string literal. This is a good example of why I add Option Explicit to the top of all of my code modules, as when you try to run the code it will direct you to this "variable" which is undefined and not intended to be a variable at all.

Additionally, when you are using With...End With you need a period . before you reference Cells, so Cells should be .Cells. This mimics the normal qualifying behavior (i.e. Sheet1.Cells.Find..)

Change Bingo to "Bingo" and change Cells to .Cells

With Sheet1
        Set FoundCell = .Cells.Find(What:="Bingo", After:=.Cells(1, 1), _
        LookIn:=xlValues, lookat:=xlPart, SearchOrder:=xlByRows, _
        SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)
    End With

If Not FoundCell Is Nothing Then
        MsgBox ("""Bingo"" found in row " & FoundCell.Row)
Else
        MsgBox ("Bingo not found")
End If

Update

In my

With Sheet1
    .....
End With

The Sheet1 refers to a worksheet's code name, not the name of the worksheet itself. For example, say I open a new blank Excel workbook. The default worksheet is just Sheet1. I can refer to that in code either with the code name of Sheet1 or I can refer to it with the index of Sheets("Sheet1"). The advantage to using a codename is that it does not change if you change the name of the worksheet.

Continuing this example, let's say I renamed Sheet1 to Data. Using Sheet1 would continue to work, as the code name doesn't change, but now using Sheets("Sheet1") would return an error and that syntax must be updated to the new name of the sheet, so it would need to be Sheets("Data").

In the VB Editor you would see something like this:

code object explorer example

Notice how, even though I changed the name to Data, there is still a Sheet1 to the left. That is what I mean by codename.

The Data worksheet can be referenced in two ways:

Debug.Print Sheet1.Name
Debug.Print Sheets("Data").Name

Both should return Data

More discussion on worksheet code names can be found here.

like image 50
Soulfire Avatar answered Oct 18 '22 22:10

Soulfire