VBA / Formula
 

Hello

I have a formula I am trying to put into vba format. I have the formula
working fine in the cells themselves, but it takes a long time to calculate
and recalculate.

Can anyone help me put this formula into the proper syntax?

There is a function called ColumnLetter that returns only the column letter.

Here is a sample formula taken from a cell: (Entered as an array formula).

=IF($H2=J$1,"-",SUM(IF(COUNTIF(OFFSET(Numbers!$B$2,ROW(INDIRECT( "2:1091"))-1,0,1,5),$H2)0,
COUNTIF(OFFSET(Numbers!$B$2,ROW(INDIRECT("2:1091") )-1,0,1,5),J$1))))

Thank you so much
Terry

VBA / Formula
 

this part looks wrong to me. you have a columnLetter and then an address instead
of a row
ColumnLetter(Range(cell.Address))

i improvised a little, but maybe it will help you

Range("F2").FormulaArray = "=If($H" & cell.Row & "=" & ColumnLetter & cell.Row _
& ", " & """-""" & ",Sum(If(Countif(Offset(Numbers!$B2,Row(Indire ct(" & _
"""2:1091""" & "))=1,0,1,5),$H" & cell.Row & _
")0,Countif(Offset(Numbers!$B$2,Row(Indirect( " & """2:1091""" &
"))-1,0,1,5)," _
& ColumnLetter & cell.Row & "))))"

--


Gary


"ITperson" wrote in message
...
Hello

I have a formula I am trying to put into vba format. I have the formula
working fine in the cells themselves, but it takes a long time to calculate
and recalculate.

Can anyone help me put this formula into the proper syntax?

There is a function called ColumnLetter that returns only the column letter.

Code:

---

x = Application.Evaluate("If($H" & cell.Row & "=" &
ColumnLetter(Range(cell.Address)) & """$1"" ,"" -
"",Sum(If(Countif(Offset(Numbers!$B2,Row(Indirect(""2:1091""))=1,0,1,5),$H" &
cell.Row &
")0,Countif(Offset(Numbers!$B$2,Row(Indirect(""2:1091""))-1,0,1,5)," &
ColumnLetter(Range(cell.Address)) & "$1))))")

Function ColumnLetter(rngCell As Range) As String
    ColumnLetter = Replace(rngCell.Address(0, 0), rngCell.Row, "")
End Function



---

Here is a sample formula taken from a cell: (Entered as an array formula).

=IF($H2=J$1,"-",SUM(IF(COUNTIF(OFFSET(Numbers!$B$2,ROW(INDIRECT( "2:1091"))-1,0,1,5),$H2)0,
COUNTIF(OFFSET(Numbers!$B$2,ROW(INDIRECT("2:1091") )-1,0,1,5),J$1))))

Thank you so much
Terry

VBA / Formula
 

As I told you on MrExcel, evaluating a formula in VBA will not be quicker
than creating WS formulae. It may be a one-off but it will also not
recalculate.

A re-design seems more appropriate as suggested by someone else.

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)



"ITperson" wrote in message
...
Hello

I have a formula I am trying to put into vba format. I have the formula
working fine in the cells themselves, but it takes a long time to
calculate
and recalculate.

Can anyone help me put this formula into the proper syntax?

There is a function called ColumnLetter that returns only the column
letter.

Code:

---

x = Application.Evaluate("If($H" & cell.Row & "=" &
ColumnLetter(Range(cell.Address)) & """$1"" ,"" -
"",Sum(If(Countif(Offset(Numbers!$B2,Row(Indirect(""2:1091""))=1,0,1,5),$H"
&
cell.Row &
")0,Countif(Offset(Numbers!$B$2,Row(Indirect(""2:1091""))-1,0,1,5)," &
ColumnLetter(Range(cell.Address)) & "$1))))")

Function ColumnLetter(rngCell As Range) As String
    ColumnLetter = Replace(rngCell.Address(0, 0), rngCell.Row, "")
End Function



---

Here is a sample formula taken from a cell: (Entered as an array formula).

=IF($H2=J$1,"-",SUM(IF(COUNTIF(OFFSET(Numbers!$B$2,ROW(INDIRECT( "2:1091"))-1,0,1,5),$H2)0,
COUNTIF(OFFSET(Numbers!$B$2,ROW(INDIRECT("2:1091") )-1,0,1,5),J$1))))

Thank you so much
Terry