Simple question concerning VBA functions

[Vepa]

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

 

[Don Guillett]

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

 

[Bernard Liengme]

I am not sure why it does not work. However, this does seem to work. Please
try it and let us know.
To ensure the loop is OK I have use MIN and MAX to evaluate start and end
values

Function trythis(cl)
start_row = WorksheetFunction.Match(cl, Sheets("Sheet1").Range("A:A"), 0)
end_row = WorksheetFunction.CountIf(Sheets("Sheet1").Range("A:A"), cl)
mystart = WorksheetFunction.Min(start_row, end_row)
myend = WorksheetFunction.Max(start_row, end_row)

With Worksheets("sheet2")
For j = mystart To myend
trythis = trythis + .Cells(j, 2)
Next j
End With

End Function


best wishes

 

[Daniel.C]

Hello.

Function testa(cl)
With Sheets("Sheet2")
Start_row = WorksheetFunction.Match(cl, .Range("A:A"), 0)
End_row = WorksheetFunction.CountIf(.Range("A:A"), cl)
testa = WorksheetFunction.Sum(Range(.Cells(Start_row, 2),
..Cells(End_row, 2)))
End With
End Function

HTH
Daniel

 

[Vepa]

Great!

With -command solved the issue.

Thanks

Br
Vepa