Searching Data using Macro
 

hi.
I have little problemo. I have data like this in excel :

Time Level (dB)
0.05 77.66
0.1 75.68
0.15 77.82
0.2 76.93
0.25 76.93
0.3 78.15
0.35 79.14
0.4 74.36
0.45 77.7
0.5 78.86
0.55 79.35
0.6 77.87
0.65 78.69
0.7 79.26
0.75 81
0.8 79.98
0.85 81.56
0.9 79.42
0.95 77.99
1 78.1
Let's say the average of the Level is X.
I want to locate the value which nearly equall, maximal 1.0 above or
1.0 below the (X-5). Can somebody help me write some excel macro for
it?

Searching Data using Macro
 

Sub FindClose()
Dim rng as Range, val as Double, eps as Double
Dim cell as Range, cell1 as Range
set rng = Range(Cells(2,2),Cells(2,2).End(xldown))
val = Application.Average(rng)
' val = val - 5 ' ???
eps = 1.0
set cell1 = rng(1)
for each cell in rng
if abs(cell-val) < eps then
set cell1 = cell
eps = abs(cell-val)
end if
Next
if eps < 1.0 then
cell1.Select
Else
Msgbox "no cell meets criteria"
End if
End Sub


--
Regards,
Tom Ogilvy

"anang" wrote in message
om...
hi.
I have little problemo. I have data like this in excel :

Time Level (dB)
0.05 77.66
0.1 75.68
0.15 77.82
0.2 76.93
0.25 76.93
0.3 78.15
0.35 79.14
0.4 74.36
0.45 77.7
0.5 78.86
0.55 79.35
0.6 77.87
0.65 78.69
0.7 79.26
0.75 81
0.8 79.98
0.85 81.56
0.9 79.42
0.95 77.99
1 78.1
Let's say the average of the Level is X.
I want to locate the value which nearly equall, maximal 1.0 above or
1.0 below the (X-5). Can somebody help me write some excel macro for
it?