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

[epactheactor]

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]
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]

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]

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.