In an excel sheet I have two rows of data. I want to search the first row for the closest value of what the user inputs into the box and then do a weighted average of five values in the column next to the column I searched. The values are all in the column to the right of the searched column and are the two rows above, the same, row and the two rows below the row of the value found in the search column. The length of the data will vary from data set to data set but the two columns within a given set will be the same length. Right now the code searches and finds the correct row and column for the closest value and outputs it as a string with the line oAd=Dn.address. I cannot get the string cell reference to change back into a range reference so that I can offset and assign values to variables to calculate the weighted average. It would be easier to have oAd=Dn.address not to be a string to begin with but I cannot figure out how to do that either. I pieced what I have together from other questions and excel help files. I know there is a better way to do this or even a simple way to fix the convoluted code I have written. I appreciate any help
Code:
Sub MG10Jun26()
Dim num As Single, Rng As Range, Dn As Range, Mx As Single, oAd As Range
Dim Nd As Range
On Error Resume Next
num = Application.InputBox(prompt:="Insert Number ", Title:="Find closest Number", Type:=1)
Set Rng = Range(Range(1, 0), Range("A" & Rows.Count).End(xlUp))
Mx = Application.Max(Rng)
For Each Dn In Rng
If Abs(num - Dn) < Mx Then
Mx = Abs(num - Dn)
oAd = Dn.Address
End If
Next Dn
Nd = Range(oAd)
Par = Nd.Offset(0, 1).Value
Paru1 = Par.Offset(-1, 0).Value
Paru2 = Par.Offset(-2, 0).Value
Pard1 = Par.Offset(1, 0).Value
Pard2 = Par.Offset(2, 0).Value
weightavg = (0.25 * Paru2 + 0.5 * Paru1 + 1 * Par + 0.5 * Pard1 + 0.25 * Pard2) / 2.5
MsgBox weightavg
End Sub
