CODEWITHSUNDEEP
excelvba
Vincent
Vincent

Reputation: 155

Convert excel formula to VBA using function

I have a FormulaArray in excel which is 

{=MAX(IF($DB$2:$DI$2<F10,$DB$2:$DI$2))}

and it worked perfectly. "F10" is equal 55, "then $DB$2:$DI$2" is a range of values between 41-102. Then i created a vba function and apply it in excel as a formula

=ClosestLessThan(F10,$DB$2:$DI$2)

but the result is #VALUE!", i cant figure out the right way. Here is what i have tried.

Function ClosestLessThan(searchNumber As Double, rangeOfValues As Range) As Double

Dim rng As Range

If rangeOfValues < searchNumber Then
Set rng = rangeOfValues
End If

ClosestLessThan = Application.WorksheetFunction.Max(rng)

End Function

Upvotes: 2

Views: 451

Answers (3)

Scott Holtzman
Scott Holtzman

Reputation: 27239

One of your biggest issues is with this line:

If rangeOfValues < searchNumber Then

While the default for a Range object is .Value, VBA cannot interpret a .Value for a multi-cell Range object.

Another issue is the way you call it. In =ClosestLessThan(F10,$DB$2:$DI$2) F10 is a cell reference (Range object), yet you assign that argument to a double data type.

Try this instead:

Function ClosestLessThan(searchNumber As Range, rangeOfValues As Range) As Double

Dim rng As Range

For each rng in rangeOfValues
     If rng < searchNumber.Value Then
         Dim rngSearch as Range
         If rngSearch is Nothing Then
            Set rngSearch = rng
         Else
            Set rngSearch = Union(rng, rngSearch)
         End If
     End If
Next

ClosestLessThan = Application.WorksheetFunction.Max(rngSearch)

End Function

And call as you do: =ClosestLessThan(F10,$DB$2:$DI$2)

Upvotes: 2

Abhinav Rawat
Abhinav Rawat

Reputation: 452

Function ClosestLessThan(searchNumber As Double, rangeOfValues As Range) As Double

Dim min As Double

min = -1


For Each cell In rangeOfValues

   If cell.value < searchNumber And cell.value > min Then
     min = cell.value
   End If
  ' Debug.Print cell.value

Next cell

ClosestLessThan = min

End Function

Upvotes: -1

shrivallabha.redij
shrivallabha.redij

Reputation: 5902

Here's how I'd go about it.

Function ClosestLessThan(dblRefVal As Double, rangeOfValues As Range) As Double
Dim rng As Range
For Each rng In rangeOfValues
    If rng.Value < dblRefVal And rng.Value > ClosestLessThan Then ClosestLessThan = rng.Value
Next
End Function

The reason why your function is not working is because it is trying to compare complete range in one go and then use MAX function.

Upvotes: 0

Related Questions