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

epactheactor

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

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.
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?
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,565
Messages
6,114,338
Members
448,570
Latest member
rik81h

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top