FormulaArray and Ranges Question

[akondra]

I am trying to replicate the array formul
{=((SUMIF(N4:N23,"<0"))/(N36*COUNT(N4:N23)))} in VBA but the range wil
change each time the program is run, so I cannot hardcode that part. I
there a way to write this to allow for range flexibility or just t
solve the formula in the code and return the value straight to th
worksheet? Thanks

 

[Guest]

Hi,
You can do something like:

Dim rg As Range
Dim strFormula as string

Set rg = Range("C1")
strFormula = "=((SUMIF(N4:N23,"<0"))/(N36*COUNT(N4:N23)))"

rg.FormulaArray = Application.ConvertFormula(strFormula, xlA1, xlR1C1)

Regards,
Sebastien

 

[Guest]

and i forgot about the dynamic range. Add the lines:
Dim newRangeAddr as string
newRangeAddr = "N4:N23"
and replace the srtFormula by:
strFormula = "=((SUMIF(" & strFormula & ",""<0""))/(N36*COUNT(" &
strFormula & ")))"

sebastien

 

[Dave Peterson]

Sebastian used a cell in a worksheet, but you could evaluate the expression
right in your macro:

Option Explicit
Sub testme01()

Dim myRng As Range
Dim myCell As Range
Dim myVal As Variant 'maybe an error??
Dim myFormula As String

With Worksheets("sheet1")
Set myRng = .Range("N4:N23") 'no idea how to set that range
'maybe??
'set myrng = .range("n4",.range("n4").end(xldown))

Set myCell = .Range("n36")

myFormula = "(sumif(" & myRng.Address & ",""<0""))" _
& "/(" & myCell.Address & "*count(" & myRng.Address & "))"

myVal = Evaluate(myFormula)

If IsError(myVal) Then
MsgBox "an error!"
Else
MsgBox myVal
End If
End With

End Sub

But I have no idea how you determine your ranges.

set myrng = .range("n4",.range("n4").end(xldown))