CODEWITHSUNDEEP
excelvba
RVH
RVH

Reputation: 67

active cell find last column in a row vba

I've got this code that finds a specific cell, and then then makes the active cell one to the right of this.

However, I want to make it so that it finds the cell with apples and bananas in it, and instead makes the active cell the cell in the last used column of the same row as the original found apples and bananas cell is in.

I hope this makes sense and I appreciate any help.

Thanks. 

Cells.Find(What:="Apples and Banana", After:=ActiveCell, LookIn:= _
    xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:= _
    xlNext, MatchCase:=False, SearchFormat:=False).Activate
    ActiveCell.Offset(0, 1).Select

Upvotes: 1

Views: 824

Answers (1)

Pierre44
Pierre44

Reputation: 1741

You can do it by finding the row of the cell with the help of find and .row and then checking the last column before activating the cell. 

Option Explicit

    Sub test()

    Dim LastCol As Long
    Dim ABRow As Long
    Dim ABrange As Range

      Set ABrange = Cells.Find(What:="Apples and Banana", After:=ActiveCell, LookIn:= _
        xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:= _
        xlNext, MatchCase:=False, SearchFormat:=False)

    If Not ABrange Is Nothing Then
        ABRow = ABrange.Row
    End If

    LastCol = Cells(ABRow, Columns.Count).End(xlToLeft).Column

    Cells(ABRow, LastCol).Activate

End Sub

Upvotes: 2

Related Questions