Function test(cl)
Application.Volatile
With Sheets("sheet2")
Start_row = WorksheetFunction.Match(cl, Sheets("Sheet1").Range("A:A"), 0)
End_row = WorksheetFunction.CountIf(Sheets("Sheet1").Range(" A:A"), cl)
test = WorksheetFunction.Sum(.Range(.Cells(Start_row, 3), .Cells(End_row,
3)))
End With
End Function

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Vepa" wrote in message
...
Hello,

I have a very simple but a painful problem concernign one VBA function
that
I have created.

My code is the following:
Function test(cl)
Start_row = WorksheetFunction.Match(cl, Sheets("Sheet1").Range("A:A"), 0)
End_row = WorksheetFunction.CountIf(Sheets("Sheet1").Range(" A:A"), cl)
test = WorksheetFunction.Sum(Sheets("Sheet1").Range(Cells (Start_row, 2),
Cells(End_row, 2)))
End Function
----
The problem is that if I change to the test row "Sheet2", suddenly I get
always #Value errors. However, when I look all the information from the
same
sheet there is no error.

Do you know, how to get the code to work so that Start_row and End_row are
looked from the Sheet1, and then the sum between these rows is calculated
from the data of sheet2?

Br
Vepa