Get row number using array VBA

Question

As you can see from the code below i m looping an array and if condition met i want to get the row number that includes the specific value in column A.

Images:

Option Explicit

Sub test()

    Dim i As Long, arr As Variant

    With ThisWorkbook.Worksheets("Sheet1")

        arr = .Range("A1:A10")

        For i = LBound(arr) To UBound(arr)

            If arr(i, 1) = 4 Then
                'Get the row that the array value apperas in Column A. The answer should be row number 8
            End If

        Next i

    End With

End Sub

Your array relates to your row number by i, although this is dependent on your array starting from the first row. If you started on the 5th row, it would be i + 5 — urdearboy
– urdearboy, Commented Mar 22, 2019 at 13:13
debug.print application.Match(4,application.transpose(arr),0) — Scott Craner
– Scott Craner, Commented Mar 22, 2019 at 13:18
Looping to find cells which satisfy a condition is often suboptimal. You could use Range.Find() in this case (or the Application.Match approach of @ScottCraner). — John Coleman
– John Coleman, Commented Mar 22, 2019 at 13:18

urdearboy · Accepted Answer · 2019-03-22 13:19:34Z

4

Your array relates to your row number by i although this is dependent on your array starting from the first row. If you started on the 5th row, it would be i + 4

    For i = LBound(arr) To UBound(arr)

        If arr(i, 1) = 4 Then
            Debug.Print i
        End If

    Next i

edited Mar 22, 2019 at 13:19

answered Mar 22, 2019 at 13:15

urdearboy

14.6k6 gold badges35 silver badges64 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

GSerg Over a year ago

It's probably then easier to loop over Range("A1:A10").Cells rather than over a copy of the values. Then you already have c.Row.

FunThomas Over a year ago

My problem with this "question" is that there is no question in the first place. The OP is answering lot's of VBA questions on SO, so I assume she/he is familiar with concepts of Debug.Print or writing a function and return a value. So it's not clear to me where the problem is...

Tim Williams · Accepted Answer · 2019-03-22 15:20:54Z

2

Sub test()

    Dim i As Long, arr As Variant, rng As Range

    Set rng = ThisWorkbook.Worksheets("Sheet1").Range("A1:A10")
    arr = rng.Value

    For i = LBound(arr) To UBound(arr)
            If arr(i, 1) = 4 Then
                Debug.Print rng(i).Row
            End If
     Next i   
End Sub

answered Mar 22, 2019 at 15:20

Tim Williams

169k8 gold badges104 silver badges143 bronze badges

Comments

Karthick Gunasekaran · Accepted Answer · 2019-03-22 14:10:23Z

1

Try with For each cells as below. it will return the exact match rows.

Option Explicit
Sub test()
    Dim i As Long
    Dim cells As Range
    With ThisWorkbook.Worksheets("Sheet1")
        For Each cells In .Range("A1:A10")
            If cells.Value = 4 Then
                MsgBox ("row Number is :" & cells.Row)
            End If
        Next
    End With
End Sub

answered Mar 22, 2019 at 14:10

Karthick Gunasekaran

2,7231 gold badge17 silver badges26 bronze badges

Comments

Foxfire And Burns And Burns · Accepted Answer · 2019-03-22 14:32:16Z

I've added a variable that stores the initial row number where the range starts.

Also, note that the index i of your array is related to position inside range. When you do arr = .Range("A1:A10") you are creating an BIDIMENSIONAL array of 10 cells (10x1). Index 1 would be Cell (1,1), index 2 would be Cell(2,1) and so on.

So the trick here would be to store the row number where range starts, and then sum up index.

Sub test()

Dim i As Long, arr As Variant

Dim rng As Range
Dim InitialRow As Long


With ThisWorkbook.Worksheets("Sheet1")
    Set rng = .Range("A1:A10")
    arr = rng.Value


    InitialRow = Range(Left(rng.Address(False, False), Application.WorksheetFunction.Search(":", rng.Address(False, False)) - 1)).Row

    For i = LBound(arr) To UBound(arr)

        If arr(i, 1) = 4 Then
            'Get the row that the array value apperas in Column A. The answer should be row number 8
            Debug.Print InitialRow + i - 1 'this is the row number that matches the value
        End If

    Next i

    Erase arr

End With

End Sub

If I test this with values on `Range("A1:A10"), I get as result 8.

But If I change position of values, I get another result with same code too, because code stores the initial row of range.

If your range is not going to change never ever the starting position, just with the index would work. But if range is not going to start always in same row, then you need to know the initial row and sum it up with the index.

Hope this code can help you out and be adapted to your needs.

Collectives™ on Stack Overflow

Get row number using array VBA

4 Answers 4

2 Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

2 Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related