+ Reply to Thread
Results 1 to 15 of 15

reading the last cell in a column

  1. 05-31-2019, 05:40 AM #1
    barrowinfurnace
    barrowinfurnace is offline
    Forum Contributor
    Join Date
    04-16-2016
    Location
    bognor
    MS-Off Ver
    Pro-Plus 2016
    Posts
    153

    reading the last cell in a column

    hi all,
    can anyone help me with this one please I am looking for a vba solution

    In column A I have an indeterminate column of data.
    In Cell B2 I have a value
    I want to find the last cell with data in it in column A.If the value of the last cell in column A corresponds with the value in Cell B2 then in cell E2 I want it to read "OK".
    If the last cell in column A does not correspond with the value of cell B2,then I want it to say"no match"Any suggestion much appreciated
    BIF
    Reply With QuoteRegister To Reply
  2. 05-31-2019, 05:57 AM #2
    TMS
    TMS is offline
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,445

    Re: reading the last cell in a column

    Why do you need VBA to do this?
    Trevor Shuttleworth - Retired Excel/VBA Consultant

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke

    Reply With QuoteRegister To Reply
  3. 05-31-2019, 06:00 AM #3
    bakerman2
    bakerman2 is offline
    Forum Guru bakerman2's Avatar
    Join Date
    10-03-2012
    Location
    Antwerp, Belgium
    MS-Off Ver
    MO Prof Plus 2016
    Posts
    6,907

    Re: reading the last cell in a column

    In WorkSheet module.

    Code will fire when changing cell B2.
    Please Login or Register  to view this content.
    Avoid using Select, Selection and Activate in your code. Use With ... End With instead.
    You can show your appreciation for those that have helped you by clicking the * at the bottom left of any of their posts.
    Reply With QuoteRegister To Reply
  4. 05-31-2019, 06:15 AM #4
    barrowinfurnace
    barrowinfurnace is offline
    Forum Contributor
    Join Date
    04-16-2016
    Location
    bognor
    MS-Off Ver
    Pro-Plus 2016
    Posts
    153

    Re: reading the last cell in a column

    Hi Bakerman,
    thanks for the code but It does not seem to be working ):
    I change the value in B2 but nothing appears in E2,I cant work out why because your code seems logical.
    any suggestions apprecited (:
    Reply With QuoteRegister To Reply
  5. 05-31-2019, 06:16 AM #5
    barrowinfurnace
    barrowinfurnace is offline
    Forum Contributor
    Join Date
    04-16-2016
    Location
    bognor
    MS-Off Ver
    Pro-Plus 2016
    Posts
    153

    Re: reading the last cell in a column

    Hi TMS,
    Thanks for your reply,are you suggesting using a formula...???Not to sure how to do that either
    regards
    BIF
    Reply With QuoteRegister To Reply
  6. 05-31-2019, 06:23 AM #6
    AliGW
    AliGW is offline
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    80,830

    Re: reading the last cell in a column

    Maybe this in E2:

    =IF(B2=LOOKUP(2,1/(A:A<>""),A:A),"OK","No Match")
    Ali

    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
    Forum Rules (updated August 2023): please read them here.
    Reply With QuoteRegister To Reply
  7. 05-31-2019, 06:24 AM #7
    bakerman2
    bakerman2 is offline
    Forum Guru bakerman2's Avatar
    Join Date
    10-03-2012
    Location
    Antwerp, Belgium
    MS-Off Ver
    MO Prof Plus 2016
    Posts
    6,907

    Re: reading the last cell in a column

    Right-click on worksheet tab of your data.

    Select View code. Paste code in Right Pane.
    Reply With QuoteRegister To Reply
  8. 05-31-2019, 06:24 AM #8
    TMS
    TMS is offline
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,445

    Re: reading the last cell in a column

    Is the data in column A numeric or alphanumeric?
    Reply With QuoteRegister To Reply
  9. 05-31-2019, 06:26 AM #9
    AliGW
    AliGW is offline
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    80,830

    Re: reading the last cell in a column

    Doesn't matter with my formula, Trevor, as long as the cells after the last value are blank.
    Reply With QuoteRegister To Reply
  10. 05-31-2019, 06:42 AM #10
    barrowinfurnace
    barrowinfurnace is offline
    Forum Contributor
    Join Date
    04-16-2016
    Location
    bognor
    MS-Off Ver
    Pro-Plus 2016
    Posts
    153

    Re: reading the last cell in a column

    Hi Bakerman

    Thanks for that it works fine now
    Kind Regards
    BIF
    Reply With QuoteRegister To Reply
  11. 05-31-2019, 06:43 AM #11
    AliGW
    AliGW is offline
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    80,830

    Re: reading the last cell in a column

    Did you look at the formula solution?
    Reply With QuoteRegister To Reply
  12. 05-31-2019, 06:46 AM #12
    barrowinfurnace
    barrowinfurnace is offline
    Forum Contributor
    Join Date
    04-16-2016
    Location
    bognor
    MS-Off Ver
    Pro-Plus 2016
    Posts
    153

    Re: reading the last cell in a column

    Hi AliGW
    Thanks for that it works great
    I posted up here and within 10 minutes I get a formula and a VBA solution to the problem....cosmic what a great site this is
    regards
    BIF
    Reply With QuoteRegister To Reply
  13. 05-31-2019, 06:47 AM #13
    AliGW
    AliGW is offline
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    80,830

    Re: reading the last cell in a column

    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.
    Reply With QuoteRegister To Reply
  14. 05-31-2019, 06:51 AM #14
    barrowinfurnace
    barrowinfurnace is offline
    Forum Contributor
    Join Date
    04-16-2016
    Location
    bognor
    MS-Off Ver
    Pro-Plus 2016
    Posts
    153

    Re: reading the last cell in a column

    Hi aliGW
    Done
    regards
    BIF
    Reply With QuoteRegister To Reply
  15. 05-31-2019, 07:03 AM #15
    TMS
    TMS is offline
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,445

    Re: reading the last cell in a column

    @Ali: yep. I always tend to use MATCH for either numeric or alphanumeric ... must break that habit. (Hence the question).
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Matrix Style Reading: Reading from one cell to cause change in another cell
    By snkhan in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 01-23-2019, 01:52 AM
  2. Reading cell/column data between worksheets problem
    By ianflexie in forum Excel General
    Replies: 2
    Last Post: 08-29-2013, 02:33 PM
  3. need help reading out multiple cells of column as a string in a single cell
    By Narayan~ in forum Excel - New Users/Basics
    Replies: 8
    Last Post: 10-19-2009, 02:14 AM
  4. Reading last non-blank cell in each row at 3 column steps
    By david55thomas in forum Excel General
    Replies: 5
    Last Post: 09-10-2009, 05:57 AM
  5. STATUS column, reading from other inputs from other column
    By tinkertron in forum Excel General
    Replies: 1
    Last Post: 07-06-2009, 04:59 PM
  6. Reading text from a column and outputing to a single cell?
    By abb16ott in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 06-01-2009, 03:38 PM
  7. Summing a Column based on reading matching items in another column
    By GSoper in forum Excel General
    Replies: 1
    Last Post: 12-18-2007, 01:59 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1