| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. 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
=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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Regression Leverage Formula (Jerry W. Lewis or Mike Middleton)already have DFITS formula | Excel Worksheet Functions | |||
| Excel 2002 Formula: Urgent Conditional Formula Required Right Away - if possible | Excel Discussion (Misc queries) | |||
| Formula expected end of statement error, typing formula into cell as part of VBA macro | Excel Programming | |||
| Cell doesn't show formula result - it shows formula (CTRL + ' doe. | Excel Worksheet Functions | |||
| Commenting custom formula fields/formula on formula editor | Excel Programming | |||
Linear Mode
