asked on

Excel VBA to get value from last column of a certain row

The below part of a code gets the cell value of the cell six columns to the right in the row where the string "Orange" appears in column A. How can this be changed to get the value of the last column in that respective row (with "Orange") as it might change frequently and is not fixed anymore. Thanks for the input and help.

(So change from currently:

aCell.Offset(, 6).Value

Open in new window

Set aCell = wb2.Sheets(1).Columns(1).Find(What:="Orange", LookIn:=xlValues, _
    LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
    MatchCase:=False, SearchFormat:=False)
    
    If Not aCell Is Nothing Then
        wb1.Sheets(1).Range("FetchedValue").Value = _
        aCell.Offset(, 6).Value
    Else
        MsgBox "Orange sales not found"
    End If

Open in new window

geoffkk

Try this
dim maxcol=wb2.Sheets(1).Columns.count
dim col as integer
with wb2.Sheets(1)
Set aCell = .Columns(1).Find(What:="Orange", LookIn:=xlValues, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False)

If Not aCell Is Nothing Then
for col=cols to 2 step -1
if not isemptyaCell.Offset(0, col-1) then
.Range("FetchedValue").Value = _
aCell.Offset(, col).Value
exit for
end if
next col
Else
MsgBox "Orange sales not found"
End If
end with

dlmille

aCell.offset(,255).end(xltoleft) is one way. Depending on Excel version, the 255 could change. The approach is to address something at the far right and use the special function.end(xlToLeft) to find that last cell on the right.

Dave

dlmille

Correction - cells(activecell.Row,255).end(xltoleft).address gives you the last rightmost column of the active cell.

so if you want to get the right most column value from aCell, you would replace:

aCell.Offset(,6).value with

Cells(acell.row,255).end(xlToLeft).value

Just tested and it works. No need for looping.

Dave

ASKER CERTIFIED SOLUTION

dlmille

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

stmoritz

ASKER

Thank you very much! Easy to apply, efficient, works perfect!