Can I use the property CurrentRegion and then a Range.Rows.Count inside a
function? Inside a Subrutine it works properly but inside a function the
counting gives 0 ... ???!!!

The code is

Function MediaCriticitÃ*(Domanda As Integer, Sottodomanda As String) As Double
Dim Nr, Nc As Integer
MacroMedia = 0
Record = 1
Set Destinaz = Worksheets("Calcoli_TMP").Range("A1")
With Sheets("Dati").Cells(1, 1)
Set Zc = .CurrentRegion
Nr = Zc.Rows.Count - 1
Nc = Zc.Columns.Count
Set RecCorr = Zc.Resize(1)
RecCorr.Copy Destinaz
For i = 1 To Nr
Set RecCorr = RecCorr.Offset(1)
If (RecCorr.Cells(4) = Domanda) And (RecCorr.Cells(5) =
Sottodomanda) And (RecCorr.Cells(6) < 0) Then
Record = Record + 1
If (RecCorr.Cells(7) = 1) Then
MacroMedia = MacroMedia + 1
Set Destinaz = Destinaz.Offset(1)
RecCorr.Copy Destinaz
End If
End If
Next

End With
MacroMedia = MacroMedia / Record
End Function

but inside a function the
counting gives 0 ... ???!!!

that would mean no. (function used in a worksheet. function used in VBA
would be no different than a subroutine)

--
Regards,
Tom Ogilvy



"Enrico" wrote in message
...
Can I use the property CurrentRegion and then a Range.Rows.Count inside a
function? Inside a Subrutine it works properly but inside a function the
counting gives 0 ... ???!!!

The code is

Function MediaCriticità(Domanda As Integer, Sottodomanda As String) As
Double
Dim Nr, Nc As Integer
MacroMedia = 0
Record = 1
Set Destinaz = Worksheets("Calcoli_TMP").Range("A1")
With Sheets("Dati").Cells(1, 1)
Set Zc = .CurrentRegion
Nr = Zc.Rows.Count - 1
Nc = Zc.Columns.Count
Set RecCorr = Zc.Resize(1)
RecCorr.Copy Destinaz
For i = 1 To Nr
Set RecCorr = RecCorr.Offset(1)
If (RecCorr.Cells(4) = Domanda) And (RecCorr.Cells(5) =
Sottodomanda) And (RecCorr.Cells(6) < 0) Then
Record = Record + 1
If (RecCorr.Cells(7) = 1) Then
MacroMedia = MacroMedia + 1
Set Destinaz = Destinaz.Offset(1)
RecCorr.Copy Destinaz
End If
End If
Next

End With
MacroMedia = MacroMedia / Record
End Function

If you are calling this function from a formula in a worksheet cell, the .Copy
statements are not legal.

On Fri, 11 Feb 2005 08:13:05 -0800, Enrico wrote:

Can I use the property CurrentRegion and then a Range.Rows.Count inside a
function? Inside a Subrutine it works properly but inside a function the
counting gives 0 ... ???!!!

The code is

Function MediaCriticità(Domanda As Integer, Sottodomanda As String) As Double
Dim Nr, Nc As Integer
MacroMedia = 0
Record = 1
Set Destinaz = Worksheets("Calcoli_TMP").Range("A1")
With Sheets("Dati").Cells(1, 1)
Set Zc = .CurrentRegion
Nr = Zc.Rows.Count - 1
Nc = Zc.Columns.Count
Set RecCorr = Zc.Resize(1)
RecCorr.Copy Destinaz
For i = 1 To Nr
Set RecCorr = RecCorr.Offset(1)
If (RecCorr.Cells(4) = Domanda) And (RecCorr.Cells(5) =
Sottodomanda) And (RecCorr.Cells(6) < 0) Then
Record = Record + 1
If (RecCorr.Cells(7) = 1) Then
MacroMedia = MacroMedia + 1
Set Destinaz = Destinaz.Offset(1)
RecCorr.Copy Destinaz
End If
End If
Next

End With
MacroMedia = MacroMedia / Record
End Function