So, I made this:
Public Function SOMAMENORES(Intervalo As Range, Menores As Integer) As Double
SOMAMENORES = 0
Dim i As Integer
i = 1
Dim MenoresAssistant As Integer
MenoresAssistant = Menores + 1
<code style="font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace; font-size: 1em; overflow: auto; tab-size: 4; background: rgb(249, 249, 249); word-wrap: normal; display: block; padding: 5px 10px; max-height: 500px;">Do Until i = MenoresAssistant
SOMAMENORES = SOMAMENORES + Application.WorksheetFunction.Small(Intervalo, i)
i = i + 1
Loop
</code>End Function
It creates a SUM of a limited smaller numbers from a range. For example =somamenores({1,2,3,4},3) results in 6
It works perfectly when I type =SOMAMENORES(...... in Excel, BUT, when I use this code:
Sub fbkestatistico()
'this sub calculates G5
LinhaMediaCria = 25
Range("G5").Select
ActiveCell.Value = "=IFERROR((2*SOMAMENORES(D13:D" & LinhaMediaCria - 1 & ",C9-1)/(C9-1))-SMALL(D13:D" & LinhaMediaCria - 1 & ",C9),"""")"
End Sub
I get the Excel error 1004: Unable to get Small property of the WorksheetFunction class
Tried using Evaluate instead of WorksheetFunction with the code below, but the function didn't work.Public Function SOMAMENORES(Intervalo As Range, Menores As Integer) As Double
SOMAMENORES = 0
Dim i As Integer
i = 1
Dim MenoresAssistant As Integer
MenoresAssistant = Menores + 1
Do Until i = MenoresAssistant
'SOMAMENORES = SOMAMENORES + Application.WorksheetFunction.Small(Intervalo, i)
SOMAMENORES = SOMAMENORES + Evaluate("SMALL(" & Intervalo & "," & i & ")")
i = i + 1
Loop
End Function
So, how can I fix this?
What am I doing wrong?
How can I make it work?
Public Function SOMAMENORES(Intervalo As Range, Menores As Integer) As Double
SOMAMENORES = 0
Dim i As Integer
i = 1
Dim MenoresAssistant As Integer
MenoresAssistant = Menores + 1
<code style="font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace; font-size: 1em; overflow: auto; tab-size: 4; background: rgb(249, 249, 249); word-wrap: normal; display: block; padding: 5px 10px; max-height: 500px;">Do Until i = MenoresAssistant
SOMAMENORES = SOMAMENORES + Application.WorksheetFunction.Small(Intervalo, i)
i = i + 1
Loop
</code>End Function
It creates a SUM of a limited smaller numbers from a range. For example =somamenores({1,2,3,4},3) results in 6
It works perfectly when I type =SOMAMENORES(...... in Excel, BUT, when I use this code:
Sub fbkestatistico()
'this sub calculates G5
LinhaMediaCria = 25
Range("G5").Select
ActiveCell.Value = "=IFERROR((2*SOMAMENORES(D13:D" & LinhaMediaCria - 1 & ",C9-1)/(C9-1))-SMALL(D13:D" & LinhaMediaCria - 1 & ",C9),"""")"
End Sub
I get the Excel error 1004: Unable to get Small property of the WorksheetFunction class
Tried using Evaluate instead of WorksheetFunction with the code below, but the function didn't work.Public Function SOMAMENORES(Intervalo As Range, Menores As Integer) As Double
SOMAMENORES = 0
Dim i As Integer
i = 1
Dim MenoresAssistant As Integer
MenoresAssistant = Menores + 1
Do Until i = MenoresAssistant
'SOMAMENORES = SOMAMENORES + Application.WorksheetFunction.Small(Intervalo, i)
SOMAMENORES = SOMAMENORES + Evaluate("SMALL(" & Intervalo & "," & i & ")")
i = i + 1
Loop
End Function
So, how can I fix this?
What am I doing wrong?
How can I make it work?