Convert excel formula to VBA using function

Question

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

@shrivallabha.redij yes there is, for simplification. the formula will be shorter and i can select the range one time and avoiding ctrl+alt+enter key and errors. — Vincent
– Vincent, Commented Dec 13, 2017 at 7:04

Scott Holtzman · Accepted Answer · 2017-12-13 07:22:58Z

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)

shrivallabha.redij · Accepted Answer · 2017-12-13 07:17:57Z

0

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.

answered Dec 13, 2017 at 7:17

shrivallabha.redij

5,9021 gold badge15 silver badges28 bronze badges

Comments

Abhinav Rawat · Accepted Answer · 2017-12-13 07:20:28Z

-1

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

edited Dec 13, 2017 at 7:20

answered Dec 13, 2017 at 7:11

Abhinav Rawat

4723 silver badges15 bronze badges

Collectives™ on Stack Overflow

Convert excel formula to VBA using function

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related