CODEWITHSUNDEEP
arraysexcelvba
Error 1004
Error 1004

Reputation: 8220

Get row number using array VBA

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:

enter image description here

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

Upvotes: 0

Views: 6056

Answers (4)

Tim Williams
Tim Williams

Reputation: 166341

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

Upvotes: 2

Foxfire And Burns And Burns
Foxfire And Burns And Burns

Reputation: 11978

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.

enter image description here

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

enter image description here

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.

Upvotes: 0

Karthick Gunasekaran
Karthick Gunasekaran

Reputation: 2713

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

Upvotes: 1

urdearboy
urdearboy

Reputation: 14580

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

Upvotes: 4

Related Questions