Executing a macro in a formula. How?

  • Thread starter Thread starter David C. Allen
  • Start date Start date
D

David C. Allen

I have a sheet with this formula in one cell: =COUNTIF('Weekly
Data'!N2:N95,"ABC")

In place of the 95 in the formula I want to call a macro that calculates it:
I have the macro coded as such:

Function CalcWeeklyRows() As Integer
CalcWeeklyRows = ActiveWorkbook.Worksheets("Weekly
Data").UsedRange.Rows.Count - 1
End Function

I can not seem to figure out the syntax for putting the call to the macro in
there; I have tried something like this:
=COUNTIF('Weekly Data'!N2:N" & CalcWeeklyRows() & ","ABC")

but Excel won't take it. I am using Excel 2000

Can someone help me with this?

Thanks!
 
try using a defined name for the range instead
insert>name>define>name it something like mycountrng
in the refers to box type
=offset($a$1,0,0,counta($a:$a),1)
modify to suit your needs
=countif(mycountrng,"abc")
or
=sumproduct((mycountrng="abc")*1)
 
Back
Top