Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 708
  • Last Modified:

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

0
stmoritz
Asked:
stmoritz
  • 3
1 Solution
 
geoffkkCommented:
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
0
 
dlmilleCommented:
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
0
 
dlmilleCommented:
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
0
 
dlmilleCommented:
Ahh it took me a minute - I knew there was a VBA variable for your version of excel...

HEre's my final answer and your SOLUTION:

replace aCell.Offset(,6).value with

Cells(aCell.Row, Cells.Columns.Count).End(xlToLeft).value

Cheers,

Dave
0
 
stmoritzAuthor Commented:
Thank you very much! Easy to apply, efficient, works perfect!
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now