+ Reply to Thread
Results 1 to 4 of 4

What VBA code would accomplish this excel formula to find the closest # to another #

  1. #1
    Registered User
    Join Date
    08-24-2012
    Location
    Columbus
    MS-Off Ver
    Excel 2010
    Posts
    46

    What VBA code would accomplish this excel formula to find the closest # to another #

    Working excel formula is as follows:
    {=INDEX(G12:G73,MATCH(MIN(ABS(G12:G73-G10)),ABS(G12:G73-G10),0))}

    Please keep in mind that the lest row will vary. In this case, it's 73, but that will change daily so that part has to be dynamic. The data will always begin with row 12 and the numbers will always be compared to the number in cell G10. Column G is static as well.

    Below is my first attempt that obviously failed.
    Please Login or Register  to view this content.
    I'm thinking there might be a better approach than all of these WorksheetFunctions so I thought I'd ask your expert opnion. Thanks in advance!

  2. #2
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,378

    Re: What VBA code would accomplish this excel formula to find the closest # to another #

    See whether this works:

    Please Login or Register  to view this content.
    If I've helped you, please consider adding to my reputation - just click on the liitle star at the left.

    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~(Pride has no aftertaste.)

    You can't do one thing. XLAdept

    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~aka Orrin

  3. #3
    Registered User
    Join Date
    08-24-2012
    Location
    Columbus
    MS-Off Ver
    Excel 2010
    Posts
    46

    Re: What VBA code would accomplish this excel formula to find the closest # to another #

    Thanks for the reply xladept. With that code, I get the message "Argument not optional" with this section ".Match". Here is the full line I have with the issue highlighted.

    Please Login or Register  to view this content.

  4. #4
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,378

    Re: What VBA code would accomplish this excel formula to find the closest # to another #

    Well I notice that:
    Please Login or Register  to view this content.
    is not an array

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Formula to find date closest to today
    By mattress58 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 04-09-2014, 12:18 PM
  2. Formula to find 3 closest values in a column when given one value
    By Brennen26 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 04-04-2014, 07:32 AM
  3. Formula to find the closest value?
    By msbing916 in forum Excel General
    Replies: 10
    Last Post: 07-03-2010, 10:50 AM
  4. Formula or Function to accomplish this?
    By elcapitan in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 03-12-2006, 05:20 AM
  5. Formula question-what I want to try to accomplish
    By Craig Fletcher in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 06-24-2005, 12:05 PM

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1