Solved: Formula in vba help

[Emoncada]

I have this formula

{=SUM(IF(FREQUENCY(IF($A$2:$A$1000<>"",IF($H$2:$H$1000="DEPOT",MATCH($A$2:$A$1000,$A$2:$A$1000,0))),ROW($A$2:$A$1000)-ROW($A$2)+1),1))}

What I am having problems is that when someone deletes a row on the spreadsheet it's changing the numbers and giving me a #REF! error on what's in bold.
"-ROW($A$2)+1),1)"

This formula is needed when they close the spreadsheet it's used from a form. Can I have it auto fill the cells when the spreadsheet is closed. So it put's the formula in right before the form pops up just in case it has the #REF! error?

Hope that's not too confusing.

[Bob Phillips]

Why do you need it to autofill, it is a count unique formula so surely you only have it once?

[Emoncada]

What is happening is every once in a while when the user closes the spreadsheet it gives them a vb error all they can do is end. The reason they are getting this error is when they close the spreadsheet a form is supposed to show the count's totals. The #REF! doesn't allow it to show. So I would like for it to do something like this.


Option Explicit
Private Sub Workbook_BeforeClose(Cancel As Boolean)

'Something like this

Range ("R1").Value = {=SUM(IF(FREQUENCY(IF($A$2:$A$1000<>"",IF($H$2:$H$1000="DEPOT",MATCH($A$2:$A$1000,$A$2:$A$1000,0))),ROW($A$2:$A$1000)-ROW($A$2)+1),1))}

Range ("S1").Value = {=SUM(IF(FREQUENCY(IF($A$2:$A$1000<>"",IF($H$2:$H$1000="VAM",MATCH($A$2:$A$1000,$A$2:$A$1000,0))),ROW($A$2:$A$1000)-ROW($A$2)+1),1))}


FrmTotals.Show

End Sub

So no matter what happens on the spreadsheet and the formula that the correct formula would be there when spreadsheet is closed.

Hope that helps.

[Bob Phillips]

What you need is



Private Sub Workbook_BeforeClose(Cancel As Boolean)
Const BASE_FORMULA As String = _
"=SUM(If(FREQUENCY(If($A$2:$A$1000<>"""",If($H$2:$H$1000=""<test>"",MATCH($A$2:$A$1000,$A$2:$A$1000,0))),ROW($A$2:$A$1000)-ROW($A$2)+1),1))"

With Worksheets("Sheet2")


.Range("R1").FormulaArray = Replace$(BASE_FORMULA, "<test>", "DEPOT")
.Range("S1").FormulaArray = Replace$(BASE_FORMULA, "<test>", "VAM")
End With

FrmTotals.Show

End Sub


You could also calculate the last row to make it a tad more robust.

[Emoncada]

Nice that seems to work good XLD thanks for the help.