REQUIRED SOME CHANGES IN VBA

[anup47]

Dear all


I've a vba which is inspired by the solution given by Hui for my other problem earlier discussed in this forum.

[pre]

Function Find_Target(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 Then
Find_Target = Target.Value - bRange
ElseIf c.Value >= Target + tRange Then
Find_Target = Target.Value + tRange
Exit Function
End If
Next

If Find_Target = "" Then Find_Target = "Not found"
End Function

[/pre]

In this Vba i'm facing a problem that it is first calculating,

If c.Value >= Target + tRange and not the sequence which i want it to calculate (i.e. for every no. in the range if it is target - brange for target +trange, which ever comes early should be calculated)

90

80

95

98

99

100

102

102

if we run the formula Find_target(D2,B$2:$B$11,10,10)where d2 contains 90 and all the numbers are written in column b, i.e. b2 to b8.

the solution should be 80 but i'm getting 100.


Regards

Anup

 

[Hui]

You need to add an Exit Function after the First Find_Target line

[pre]

Function Find_Target(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 Then
Find_Target = Target.Value - bRange
Exit Function
ElseIf c.Value >= Target + tRange Then
Find_Target = Target.Value + tRange
Exit Function
End If
Next

If Find_Target = "" Then Find_Target = "Not found"
End Function

[/pre]