Results 1 to 3 of 3

Thread: VBA to loop through one range to find closest value in another range
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Sep 2015
    Posts
    37
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default VBA to loop through one range to find closest value in another range

    Hello.

    I know this has to be answered before but I couldn't find anything in the forums.

    What I am trying to do is this...I have two named ranges, LR and LR2, each on a different sheet. I'm trying to create a If and For loop that would go through range LR and match it with a value in LR2 IF the value is close to it.

    I tried to do Vlookup on this, but it kept giving me an error so I'm trying a different route. I know I could do a simple code like what is below if LR2 was a static number, but it's a range.


    Code:
    Code:
    MX = Application.Max(LR)
    For Each L In LR    If Abs(LR2 - L) < MX Then
            MX = Abs(LR2 - L)
            oAd = LR2.Address
            Range(L).Offset(0, lastcolumn + 2).Value = oAd
        End If
    Next L
    Any help would be very grateful.

  2. #2
    MrExcel MVP
    Junior Admin
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    50,814
    Post Thanks / Like
    Mentioned
    57 Post(s)
    Tagged
    11 Thread(s)

    Default Re: VBA to loop through one range to find closest value in another range

    I'm trying to create a If and For loop that would go through range LR and match it with a value in LR2 IF the value is close to it.
    I think this needs to be defined a little better for us.

    1. What is the size of these ranges? Are they multiple columns AND rows?
    2. Please post some sample of what data in these ranges looks like?
    3. For matching a value "close to it", what constitutes "close enough"? Or are you just wanting the closest value?
    4. Once you find the value that you are looking for, what do you want to happen?
    TIPS FOR FINDING EXCEL SOLUTIONS
    1. Use the built-in Help that comes with Excel/Access
    2. Use the Search functionality on this board
    3. A lot of VBA code can be acquired by using the Macro Recorder.

    "Give a man a fish, feed him for a day. Teach a man to fish, feed him for life!"

  3. #3
    New Member
    Join Date
    Sep 2015
    Posts
    37
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA to loop through one range to find closest value in another range

    Hello and thank you for replying.

    1. The size of the ranges would differ depending on the data provided but for an example LR = Sheet1(E13:E5000) and LR2 = Sheet2(C3:C200).
    2. The data in both would be numbers (footage). starting at single digits and going to 6 digits (0 to 123456).
    3. Close enough would be within +-2 feet. (this can be open)
    4. If there is a match I was going to use the offset property to provide the corresponding data in LR2 (5 columns to the right) and put it in the first empty columns in Sheet1 (LR's sheet)


    I guess a way to think of what I am doing is that I have data in Sheet1 that marks every mile in a trip. Sheet2 would have at what mile are the exits and I'm trying to put the data from sheet 2 to sheet 1.

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
  •