Please provide a customized vba so that it can be used for other cell ranges. i.e instead of a1 to a100 can be used for g5 to g200 etc.

- Thread starter anup47
- Start date

Please provide a customized vba so that it can be used for other cell ranges. i.e instead of a1 to a100 can be used for g5 to g200 etc.

firstly welcome to the Chandoo.org forums

I looked at a formula solution, but decided a UDF solution would be quicker to write

Below find some code for a UDF which you need to copy and paste into a Code Module in VBA

to use enter as a Function in C1

=Find_Near(Vale, Range, [Variance])

The Variance is optional and defaults to 0

eg:

=Find_Near(90, A1:A200, 2)

will find the first value between 88 and 92 in range A1:A200

=Find_Near(B1, Data, 5)

will find the first value between B1-5 and B1 +5 in the named range "Data"

=Find_Near(800, Data, 5)

will find the first value between 795 and 805 in the named range "Data"

If the highest value in Data is < 795 or lowest value is > 805 it will return a "Not Found" message

Code

[pre]

Code:

```
Function Find_Near(Target, Data, sRange) As Variant
Dim c As Range
For Each c In Data
If c.Value >= Target - sRange And c.Value <= Target + sRange Then
Find_Near = c.Value
Exit Function
End If
Next
Find_Near = "Not Found"
End Function
```

The solution you provided is awesome, but i would like you to make some changes so that it can become a working tool for me.

Let me clarify my problem.

let's say from a1 to a5 cell we have 100, 101, 102, 103, 104, and then from a6 to a10 its 90, 108, 110, 115, 120

Now i want your Find_near function should work in such a manner that when i write Find_near(100,a1:a10,10) i should get 90 as it is (100-10) if 110 would have come earlier in lets say a5 the answer should be 110 as it is (100+10) and it has came earlier than 90.(i don't want the number which i write in the formula, here it is 100. I want the formula should deliver the +10 or - 10 from 100, whichever is earlier in the range)

Also if possible please make the formula in such a way where if 90 is not there but lets say 88 or 89 or even 89.9 is given, it should pick up the number just below 90, and in case of 100 may be 100.1 or 100.2 or 101 which ever is there (greater than 100)

with regards

Anup

so in your list

[pre]

Code:

```
A1, A2, A3, A4, A5, A6, A7, A8, A9, A10
100, 101, 102, 103, 104, 90, 108, 110, 115, 120
So Find_near(100,a1:a10,10), finds 100 as it is first in the list
Do you mean lowest number within a variance, in this case it should be 90?
then the following code will do that
Function Find_Near(Target, Data, sRange) As Variant
Dim c As Range
Dim low As Double
Dim FindLow As Boolean
FindLow = False
For Each c In Data
If c.Value >= Target - sRange And c.Value <= Target + sRange Then
If c.Value < low Or FindLow = False Then
low = c.Value
FindLow = True
End If
End If
Next
If FindLow = True Then
Find_Near = low
Else
Find_Near = "Not Found"
End If
End Function
```

100

101

102

90

91

110

Here the formula gives me 100 but it should give me 90 as (100-10 i.e. 90 is coming below 100+10 i.e. 110). It should not show 100 as i want + 10 or -10 divergence.

with regards

Anup

"Do you mean lowest number within a variance, in this case it should be 90?

then the following code will do that"

[pre]

Code:

```
Function Find_Near(Target, Data, sRange) As Variant
Dim c As Range
Dim low As Double
Dim FindLow As Boolean
FindLow = False
For Each c In Data
If c.Value >= Target - sRange And c.Value <= Target + sRange Then
If c.Value < low Or FindLow = False Then
low = c.Value
FindLow = True
End If
End If
Next
If FindLow = True Then
Find_Near = low
Else
Find_Near = "Not Found"
End If
End Function
```

I wrote a vba that speaks what i want.

Function Speak(c As Boolean, s As String)

If c Then Application.Speech.Speak s

Speak = c

End Function

=speak(a1>a2,rept(c1,3)), here it speaks 3 times (cell c1)if a1>a2. Now the problem is if again and again my a1 and a2 is changing it starts speaking. NOw i want that even if a1 and a2 changes and a1 becomes greater than a2 it should atleast repeat after lets say 60 seconds or so.

with regards

Anup

with regards

Anup

Do you only want the Values at Value+/- Variance ?

Not the in-between values?

eg: 100+/-10, you only want to look for 90 and 110 ?

Because in the above list the first 100 is in the Range 100+/-10, which is what my original code is looking for

If you only want the Value +/-Variance

then this will do it

[pre]

Code:

```
Function Find_Near(Target, Data, sRange) As Variant
Dim c As Range
For Each c In Data
If c.Value = Target - sRange Or c.Value = Target + sRange Then
Find_Near = c.Value
End If
Next
If Find_Near = "" Then Find_Near = "Not found"
End Function
```

Re: Speak

Please start new questions in new posts

It makes searching and helping a lot easier

Have a look at http://www.ozgrid.com/Excel/run-macro-on-time.htm

which will walk you through running macros at set times

Now the only problem i'm facing is if my series contains lets say (100, 99, 98, 95, 89, 80) and if i use =find_near(100,a1:a6,10) it will show me Not found, Rather it should show 89 as it is just lower than 90, and for an other series(100, 99, 98, 95, 112, 115)then it will show me Not found, Rather it should show 112 as it is just above 110 as 110 is not present.

also if both for instance are there in the series (100,101,102,110, 90)i.e. 110 and 90 then our formula should give 110 not 90, as 110 is there in the series before 90.

I hope i'm able to explain you my problem this time.

with regards

Anup

As only one formula will give us the answer either 100 - 10 or 100 + 10, so which ever condition is satisfied earlier should be shown by the formula.

with regards

Anup

Find_near(100,a1:a10,10,2)

Will look for 90 and 110 and if not found

search for the closest number to 90 and 110 in the range 88-92 & 108-112

[pre]

Code:

```
Function Find_Near(Target, Data, sRange, Optional tRange As Double = 0) As Variant
Dim c As Range
Dim NR As Double, chk As Double
For Each c In Data
If c.Value = Target - sRange Or c.Value = Target + sRange Then
Find_Near = c.Value
End If
Next
For Each c In Data
If c.Value = Target - sRange Or c.Value = Target + sRange Then
Find_Near = c.Value
End If
Next
If Find_Near = "" Then
chk = 999999
For Each c In Data
If (c.Value >= Target - sRange - tRange) And _
(c.Value <= Target - sRange + tRange) Or _
(c.Value >= Target + sRange - tRange) And _
(c.Value <= Target + sRange + tRange) Then
If Application.WorksheetFunction.Min(Abs(c.Value - (Target - sRange)), Abs(c.Value - (Target + sRange))) < chk Then
Find_Near = c.Value
End If
End If
Next
End If
If Find_Near = "" Then Find_Near = "Not found"
End Function
```

Ok now i'll explain my original problem.

lets say i bought a share on day 1, @ rs. 100. Now my stop loss for this share is rs. 90.

i.e. as soon as it will reach 90 or below i'll sell my share and book the loss. the loss will be (100 - 90). Now i also want that if 110 comes before 90 i will sell the share @110 and book profit. the profit will be (110-100). Now on daily basis by share prices are written from a1 to a100, say(100, 98,99,109, 93, 89, 105, 110, .....), here my stoploss will arrive before i make a profit since first 89 has come and that also before 110, so my loss would be (100-89) i.e. rs. 11. but fortunately if 110 or 111 would have come before 90 then i would sell it at 110, then i would make a profit of rs 10 (110-100).

So now i want find_near(100, a1:a100,10) should give me which ever comes earlier 90 or 110. so that i can know that my stoploss has come early of profit has come early.

with regards

Anup

I have also allowed you to vary the downside and upside independently

to use enter as a Function in C1

=Find_Near(Value, Range, Upper Variance, [Lower Variance])

The Lower variance is optional and defaults to the Upper variance if not supplied

eg:

=Find_Near(90, A1:A200, 10)

will find the first value >= 100 or <=80 in range A1:A200

=Find_Near(B1, Data, 5)

will find the first value <= B1-5 or >= B1 +5 in the named range "Data"

=Find_Near(90, A1:A200, 10, 20)

will find the first value >= 100 or <=70 in range A1:A200

note: difference in Lower and Upper values

Code

[pre]

Code:

```
Function Find_Near(Target, Data, tRange, Optional bRange = 0) As Variant
Dim c As Range
If bRange = 0 Then bRange = tRange
For Each c In Data
If c.Value <= Target - bRange Or c.Value >= Target + tRange Then
Find_Near = c.Value
Exit Function
End If
Next
If Find_Near = "" Then Find_Near = "Not found"
End Function
```