CODEWITHSUNDEEP
vbaexcel
phillipsK
phillipsK

Reputation: 1516

Identify vertical cell with SearchDirection

I need to identify the dividend income row within the column Ledger account. Here is the data beginning in Column A Cell A1:

Ledger Account
Prior Shares Outstanding
Current Shares Outstanding
Current Share Activity
Dividend Income

Eventually I want to put in an array the adjacent data within the Dividend Income Row (i.e. offset 1, 2, 3 cells etc..)

This is what I have - How could I modify the direction to search downwards after identifying the appropriate column (Ledger Account) and store the values in row 5 (Dividend Income row) within an an array?

cName = "Ledger Account"
cA = Sheets(1).Rows.Find(What:=UCase(cName), LookAt:=xlWhole, SearchDirection:=xlNext).Column

Upvotes: 1

Views: 70

Answers (2)

Excel Hero
Excel Hero

Reputation: 14764

This is all you need to get the values from the Dividend Income row into an array:

Public Sub Sample()
    Dim v
    v = [index(a:a,match("dividend income",a:a,))].EntireRow
End Sub

With the above, v becomes a 2d array with all of the values from that row. For example, v(1, 1) contains 'Dividend Income' and v(1, 2) contains the value immediately to the right, and so forth.

This is an efficient approach. One variable (which is the array you want) and just one call across the boundary between Excel and VBA.

The above focuses directly at how to get the array. Of course, in practice one should make allowance for the case where the search text is not found and so this version does so:

Public Sub Sample()
    Dim v
    On Error Resume Next
    v = [index(a:a,match("dividend income",a:a,))].EntireRow
    If Err Then MsgBox "No match."
End Sub

Upvotes: 0

Siddharth Rout
Siddharth Rout

Reputation: 149305

Is this what you are trying? I have commented the code so you shouldn't have a problem understanding it. But if you still do then simply ask :)

Sub Sample()
    Dim ws As Worksheet
    Dim MyAr As Variant
    Dim SearchString As String
    Dim aCell As Range
    Dim i As Long

    SearchString = "Ledger Account"

    '~~> Change this as applicable
    Set ws = ThisWorkbook.Sheets("Sheet1")

    With ws
        Set aCell = .Columns(1).Find(What:=SearchString, LookIn:=xlValues, _
                    LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
                    MatchCase:=False, SearchFormat:=False)

        If Not aCell Is Nothing Then
            '~~> 5 Denotes the 5th column i.e Column "E"
            '~~> Amend as applicable
            '~~> Store the values From say Col B to E in the array
            MyAr = Application.Transpose( _
                                         .Range(.Cells(aCell.Row, 2), _
                                                .Cells(aCell.Row, 5) _
                                                ).Value _
                                        )

            '~~> Check what is in the array
            For i = LBound(MyAr) To UBound(MyAr)
                Debug.Print MyAr(i, 1)
            Next i
        End If
    End With
End Sub

Upvotes: 1

Related Questions