Code to replace formula
Results 1 to 6 of 6

Thread: Code to replace formula

  1. #1
    Board Regular
    Join Date
    Oct 2006
    Posts
    2,284
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

    Default Code to replace formula

    Hi all,

    I have the following formula that searches to find the latest row of data

    Column C is the date
    Column F is the job ref
    Column G is the article ref

    IF(AND(MAX(($C$12:$C$10023*($F$12:$F$10023=F322)*($G$12:$G$10023=G322)))=C322,C322<>""),"Latest","")

    I have this on a long list and have to drag down the formula to 7000, plus rows

    Is there a way to replicate this as code as to not slow up the sheet when typing?

    Many thanks

  2. #2
    Board Regular alansidman's Avatar
    Join Date
    Feb 2007
    Location
    Steamboat Springs
    Posts
    4,862
    Post Thanks / Like
    Mentioned
    7 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Code to replace formula

    If you are trying to find the last row in the column C, then the following code will work

    Code:
    Sub FindLast()
    Dim lr as long
    lr = Range("C" & rows.count).end(xlup).row
    Msgbox ("Last Row is " & lr)
    End Sub
    Let me know if that works for you
    Alan

    Am Yisrael Chai

    Win 10--Office 2019
    When Posting Code, please use code tags.
    How to insert Mcode to Power Query https://excel.solutions/2017/11/powe...te-code-video/


  3. #3
    Board Regular
    Join Date
    Oct 2006
    Posts
    2,284
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Code to replace formula

    Hi Alansidman,

    Apologies here but I may have mislead you with my requirements

    The formula looks for the last entry for each job ref and shows this row by "Latest"

    there may be many different entries with the same job refence but would like to see the latest entry as a quick reference

    Thanks

  4. #4
    MrExcel MVP
    Moderator
    Peter_SSs's Avatar
    Join Date
    May 2005
    Location
    Macksville, Australia
    Posts
    40,120
    Post Thanks / Like
    Mentioned
    82 Post(s)
    Tagged
    19 Thread(s)

    Default Re: Code to replace formula

    Before moving to code, you could try a formula like this. For me, it is about 5 times faster than your existing formula.
    I have assumed that the formula quoted actually starts in row 12 with references to F12, G12 and C12
    If that is so, you could try this in row 12 instead and copy down.

    =IF(COUNTIFS(F12:F$10023,F12,G12:G$10023,G12)=1,"Latest","")
    Hope this helps, good luck.
    Peter
    Excel 365 - Windows 10
    - Want to help your helpers by posting a small, copyable, screen shot directly in your post? Look here
    - If posting vba code, please use Code Tags - like this [code]Paste code here[/code] - or use the VBHTML Maker
    - Read: Forum Rules, Forum Use Guidelines, & FAQ

  5. #5
    Board Regular
    Join Date
    Oct 2006
    Posts
    2,284
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Code to replace formula

    That's brilliant thanks Peter

  6. #6
    MrExcel MVP
    Moderator
    Peter_SSs's Avatar
    Join Date
    May 2005
    Location
    Macksville, Australia
    Posts
    40,120
    Post Thanks / Like
    Mentioned
    82 Post(s)
    Tagged
    19 Thread(s)

    Default Re: Code to replace formula

    You are very welcome. Thanks for letting us know.
    Hope this helps, good luck.
    Peter
    Excel 365 - Windows 10
    - Want to help your helpers by posting a small, copyable, screen shot directly in your post? Look here
    - If posting vba code, please use Code Tags - like this [code]Paste code here[/code] - or use the VBHTML Maker
    - Read: Forum Rules, Forum Use Guidelines, & FAQ

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
  •