Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

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

Posted on 2011-02-14
5
Medium Priority
?
702 Views
Last Modified: 2012-05-11
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
Comment
Question by:stmoritz
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
5 Comments
 
LVL 2

Expert Comment

by:geoffkk
ID: 34893390
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
 
LVL 42

Expert Comment

by:dlmille
ID: 34893399
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
 
LVL 42

Expert Comment

by:dlmille
ID: 34893423
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
 
LVL 42

Accepted Solution

by:
dlmille earned 500 total points
ID: 34893446
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
 

Author Closing Comment

by:stmoritz
ID: 34895180
Thank you very much! Easy to apply, efficient, works perfect!
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

721 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question