# vba for range

#### anup47

##### Member
In excel i have different number from lets say 10 to 200 in random basis from cell a1 to a100. I have a number in b1 which is say 20. Now i want to know if i start from cell a1 to a100 which number comes early (b1- 2, or b1+2) i.e. 18 or 20. Now whichever number comes early i want it at cell c1.

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.

#### Hui

##### Excel Ninja
Staff member
Anup47

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

Use

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 &#60; 795 or lowest value is &#62; 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 &#62;= Target - sRange And c.Value &#60;= Target + sRange Then
Find_Near = c.Value
Exit Function
End If
Next
End Function``````
[/pre]

#### anup47

##### Member

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

#### Hui

##### Excel Ninja
Staff member
When you say earlier I have assumed that the first item in the list that is +/- the variance is earlier, and that is what the code does.

[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 &#62;= Target - sRange And c.Value &#60;= Target + sRange Then
If c.Value &#60; low Or FindLow = False Then
low = c.Value
FindLow = True
End If
End If
Next
If FindLow = True Then
Find_Near = low
Else
End If
End Function``````
[/pre]

#### anup47

##### Member

here i want variance should be +10 or -10 which ever is early.

(i.e. if 90 comes early solution would be 90 but if 110 comes early 110 would be the solution). here we assume a1 to be first and a10 would be the last.

with regards

Anup

#### Hui

##### Excel Ninja
Staff member
My first solution does exactly that?

#### anup47

##### Member
Sorry to bother you again, but let me tell you your first solution is not working. See the example i'm showing below.

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

#### Hui

##### Excel Ninja
Staff member

"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 &#62;= Target - sRange And c.Value &#60;= Target + sRange Then
If c.Value &#60; low Or FindLow = False Then
low = c.Value
FindLow = True
End If
End If
Next
If FindLow = True Then
Find_Near = low
Else
End If
End Function``````
[/pre]

#### anup47

##### Member
I've got one more problem, i wish you could help me on this.

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&#62;a2,rept(c1,3)), here it speaks 3 times (cell c1)if a1&#62;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

#### anup47

##### Member
and for your earlier reply let me tell you i don't want lowest of the variance. i want, lets say we have a series of numbers (100,101,102,103,104,90,91,110). here the series contains both 90 and 110. so if we are talking about the (100+10 or 100-10) both are available. I want that no. which comes first in the series, here starting with 100, we arrive at 90 before 110, so your formula should give 90, and no other no. but if 110 would have come before 90 it should show 110.

with regards

Anup

#### Hui

##### Excel Ninja
Staff member
Re: (100,101,102,103,104,90,91,110)

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
End Function``````
[/pre]
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

#### anup47

##### Member
Thank you very much, now its working perfectly fine.

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

#### Hui

##### Excel Ninja
Staff member
How much variance do you want to allow ?

#### anup47

##### Member
the variance is not the problem, say if we are looking at 100 - 10 i.e. 90 and 90 is not available then it should show the next available number below 90, it could be 89.9, 88.9, 89, 88 which ever comes earlier, and in case of 100 + 10 it should be 110 or above 110 whichever comes earlier.

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

#### Hui

##### Excel Ninja
Staff member
Ok, try this mod

Find_near(Target, Range, Variance, Error)

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

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 = &#34;&#34; Then
chk = 999999
For Each c In Data
If (c.Value &#62;= Target - sRange - tRange) And _
(c.Value &#60;= Target - sRange + tRange) Or _
(c.Value &#62;= Target + sRange - tRange) And _
(c.Value &#60;= Target + sRange + tRange) Then

If Application.WorksheetFunction.Min(Abs(c.Value - (Target - sRange)), Abs(c.Value - (Target + sRange))) &#60; chk Then
Find_Near = c.Value
End If

End If
Next
End If

End Function``````
[/pre]

#### anup47

##### Member
No sir its not working.

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

#### Hui

##### Excel Ninja
Staff member
This will do what you want

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

Use

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 &#62;= 100 or &#60;=80 in range A1:A200

=Find_Near(B1, Data, 5)

will find the first value &#60;= B1-5 or &#62;= B1 +5 in the named range "Data"

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

will find the first value &#62;= 100 or &#60;=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 &#60;= Target - bRange Or c.Value &#62;= Target + tRange Then
Find_Near = c.Value
Exit Function
End If
Next

End Function``````
[/pre]

#### anup47

##### Member
thank you very much for your support and patience , i don't have words to describe how much i have learnt from this discussion.

with regards

Anup