CODEWITHSUNDEEP
excelvba
abcdefg12345
abcdefg12345

Reputation: 77

Find the row and column of a range object

First I look to search for the range of a cell that corresponds to a value I want to find:

Dim a As Range
Set a = ActiveSheet.Range("A:A").Find("date", LookIn:=xlValues)

Then what I'd like is something like:

a.row
a.Column

But there is a Compile error: method not valid without suitable object

Is there a way to find the row and column numbers of a range object that is obtained from searching for a cell value?

Upvotes: 1

Views: 820

Answers (2)

J.schmidt
J.schmidt

Reputation: 719

By using the debug.print or MsgBox function you can find out the row and column: 

Sub findcell()

Dim a As Range
Set a = ActiveSheet.Range("A:A").Find("date", LookIn:=xlValues)

if Not(a is nothing) then
    Debug.Print "row of cell: " & a.Row
    Debug.Print "column of cell: " & a.Column
    MsgBox "row of cell: " & a.Row
    MsgBox "column of cell: " & a.Column
else: Msgbox "Cell not found"
end if

End Sub

Edit: I edited the code with a if-branch, considering what Harassed mentioned

Upvotes: 0

VBasic2008
VBasic2008

Reputation: 54767

Range Compile Error

The compile error occurred because you cannot just write a.Row or a.Column, you have to use an equation or use it in a function. Find always returns either Nothing or a cell range which has its properties. When you have to use its row or column, then you write e.g. 

Dim b as Long
b = a.Row

or to write to the same cell in another worksheet:

Worksheets("Sheet2").Range(a.Row, a.Column) = 25

To check if Find has found the value use the following code pattern.

The Code

Sub CellRangeUsage

    Dim a As Range

    Set a = ActiveSheet.Range("A:A").Find("date", LookIn:=xlValues) 

    If Not a Is Nothing then ' Check if value was found.
        MsgBox "'date' was found in row " & a.Row & " of column " & a.Column & "."
      Else
        MsbBox "'date' was not found."
    End If

End Sub

Upvotes: 1

Related Questions