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

epactheactor

New Member
Joined
Sep 9, 2015
Messages
37
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
[/CODE]

Any help would be very grateful.
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
51,783
Office Version
365
Platform
Windows
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?
 

epactheactor

New Member
Joined
Sep 9, 2015
Messages
37
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.
 

Forum statistics

Threads
1,085,676
Messages
5,385,146
Members
401,935
Latest member
jgodonnell

Some videos you may like

This Week's Hot Topics

Top