DSM
DSM

Reputation: 257

Getting row number of a cell in respect to a specified range

I am trying to get the row number of a cell in a range that I specify with vba. I want to get the row number of the cell in the range but what I am getting is the row number of the cell in the worksheet.

This is the vba code I am using currently. I have set a range and within the range I search for a cell that contains the text string "C". Once I have found the cell I want to make changes to the value in the second column of the range.

Sub trial2()

Dim ActiveWB As Workbook
Dim ActiveWS As Worksheet
Dim rng As Range, findCell As Range

Set ActiveWB = ActiveWorkbook
Set ActiveWS = ActiveWB.ActiveSheet

Set rng = ActiveWS.Range("B4", "C10")

With rng
    Set findCell = .Cells.Find(what:="C")
End With

rng.Cells(findCell.Row, 2).Value = "change to something."

End Sub

Before running the code:

enter image description here

After running the code:

enter image description here

the cell value that contains "C" is in the 6th row of the worksheet, but in the 3rd row of the range. I was wondering how do I get the 3rd row of the range. I am aware that I can just offset the cell by 1 column to solve this problem, but I am curious about getting row numbers of cells in respect to a defined range.

Upvotes: 1

Views: 4077

Answers (2)

Error 1004
Error 1004

Reputation: 8220

You can use:

Option Explicit

Sub test()

    Dim rng As Range

    With ThisWorkbook.Worksheets("Sheet1")

        'If your searching area for A,B,C etc is from B4 to B10 just use
        Set rng = .Range("B4:B10").Cells.Find(what:="C")
        'If your searching area for A,B,C etc is from B4 to C10 just use
        Set rng = .Range("B4:C10").Cells.Find(what:="C")

        If Not rng Is Nothing Then
            'Way 1
            .Cells(rng.Row, rng.Column + 1).Value = "change to something."
            'Way 2
            rng.Offset(0, 1).Value = "change to something."
        Else
            MsgBox "No ""C"" found."
        End If

    End With

End Sub

Upvotes: 1

BigBen
BigBen

Reputation: 49998

One option is to adjust based on the number of the first row of rng, using simple math.

rng.Cells(findCell.Row - rng.Row + 1, 2).Value = "change to something."

In this case 6 - 4 + 1 = 3.

Upvotes: 2

Related Questions