FormulaArray and Ranges Question
 

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

--
akondr
-----------------------------------------------------------------------
akondra's Profile: http://www.excelforum.com/member.php...fo&userid=1514
View this thread: http://www.excelforum.com/showthread.php?threadid=26781

FormulaArray and Ranges Question
 

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

"akondra" wrote:


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


--
akondra
------------------------------------------------------------------------
akondra's Profile: http://www.excelforum.com/member.php...o&userid=15147
View this thread: http://www.excelforum.com/showthread...hreadid=267816

FormulaArray and Ranges Question
 

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

"sebastienm" wrote:

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

"akondra" wrote:


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


--
akondra
------------------------------------------------------------------------
akondra's Profile: http://www.excelforum.com/member.php...o&userid=15147
View this thread: http://www.excelforum.com/showthread...hreadid=267816

FormulaArray and Ranges Question
 

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))



akondra wrote:

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

--
akondra
------------------------------------------------------------------------
akondra's Profile: http://www.excelforum.com/member.php...o&userid=15147
View this thread: http://www.excelforum.com/showthread...hreadid=267816

--

Dave Peterson