Results 1 to 7 of 7

Thread: VBA Help - Modify a LastRow Lookup line of code - Excel 2016
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular Johnny Thunder's Avatar
    Join Date
    Apr 2010
    Location
    California
    Posts
    554
    Post Thanks / Like
    Mentioned
    5 Post(s)
    Tagged
    0 Thread(s)

    Default VBA Help - Modify a LastRow Lookup line of code - Excel 2016

    Hello All,

    I have a piece of code that I need to modify since it works so well, it essentially is a Last Row Lookup (Column Based) and defines the last row with an actual formula result.


    What I need: I have a variable to replace the column letter "ColumnL" that I need to update the line of code but it doesn't seem to work with different variations that I have tried.

    Line: cSht.[match(2,1/(AP:AP<>0))]

    cSht = my Variable Sheet

    Any help is appreciated.
    Regards,
    JT

    Please follow the forum Rules and Guidelines

    To post a screenshot try one of these links

    MrExcel HTML Maker, RoryA addin (Win & Mac) or Borders-Copy-Paste

  2. #2
    Board Regular
    Join Date
    Sep 2016
    Posts
    2,572
    Post Thanks / Like
    Mentioned
    36 Post(s)
    Tagged
    1 Thread(s)

    Default Re: VBA Help - Modify a LastRow Lookup line of code - Excel 2016

    It's not clear what you want to do.
    If you want to find the last row number with data in column AP :
    Code:
    LR=Cells(Rows.count,"AP").End(xlUp).Row

  3. #3
    Board Regular Johnny Thunder's Avatar
    Join Date
    Apr 2010
    Location
    California
    Posts
    554
    Post Thanks / Like
    Mentioned
    5 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA Help - Modify a LastRow Lookup line of code - Excel 2016

    I thought my post was extremely clear. I am looking to modify my already working code to replace the manual "AP" column designation to my dynamic variable "ColumnL"

    Also, the code you posted results in 1, there is over 1000 rows of data and the last cell that resulted in a value ends at 617. My line of code works great, I just don't know how to modify it to include my variable instead of hardcoding the range like "AP:AP".

    Thank you for trying though. This is one of the final pieces of my code and I am done.
    Regards,
    JT

    Please follow the forum Rules and Guidelines

    To post a screenshot try one of these links

    MrExcel HTML Maker, RoryA addin (Win & Mac) or Borders-Copy-Paste

  4. #4
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    28,173
    Post Thanks / Like
    Mentioned
    470 Post(s)
    Tagged
    47 Thread(s)

    Default Re: VBA Help - Modify a LastRow Lookup line of code - Excel 2016

    Maybe
    Code:
    Dim Clmn As String
    Clmn = "AP:AP"
    Debug.Print csht.Evaluate("match(2,1/(" & Clmn & "<>0))")
    But it's a very slow way of doing it.
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  5. #5
    Board Regular Johnny Thunder's Avatar
    Join Date
    Apr 2010
    Location
    California
    Posts
    554
    Post Thanks / Like
    Mentioned
    5 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA Help - Modify a LastRow Lookup line of code - Excel 2016

    Woohoo! That is exactly what I needed!

    May I ask, whats the line about evaluate? And what was the need for the [ ] brackets used in the original version of the formula? I found it on this site a few days back and wasn't really sure how it worked but it works well.
    Regards,
    JT

    Please follow the forum Rules and Guidelines

    To post a screenshot try one of these links

    MrExcel HTML Maker, RoryA addin (Win & Mac) or Borders-Copy-Paste

  6. #6
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    28,173
    Post Thanks / Like
    Mentioned
    470 Post(s)
    Tagged
    47 Thread(s)

    Default Re: VBA Help - Modify a LastRow Lookup line of code - Excel 2016

    You're welcome & thanks for the feedback
    The [] is a "shorthand" way of using Evaluate, but I don't think you can use it with variables.
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  7. #7
    Board Regular
    Join Date
    Sep 2016
    Posts
    2,572
    Post Thanks / Like
    Mentioned
    36 Post(s)
    Tagged
    1 Thread(s)

    Default Re: VBA Help - Modify a LastRow Lookup line of code - Excel 2016

    Quote Originally Posted by Johnny Thunder View Post
    I thought my post was extremely clear. I am looking to modify my already working code to replace the manual "AP" column designation to my dynamic variable "ColumnL"

    Also, the code you posted results in 1, there is over 1000 rows of data and the last cell that resulted in a value ends at 617. My line of code works great, I just don't know how to modify it to include my variable instead of hardcoding the range like "AP:AP".

    Thank you for trying though. This is one of the final pieces of my code and I am done.
    If the code I posted assigns a value of 1 to LR, either there is no data in column AP or data in AP1 only.
    Although your code is now working, it's better to avoid using worksheet formulas in code unnecessarily.
    This is better/standard :
    Code:
    Dim Clmn As String, LR#
    Clmn = "AP:AP"
    LR = Cells(Rows.Count, Clmn).End(xlUp).Row
    There are other ways which avoid using a worksheet function :
    https://www.google.com/search?q=exce...hrome&ie=UTF-8

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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