Simple question concerning VBA functions
 

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

Simple question concerning VBA functions
 

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

Simple question concerning VBA functions
 

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
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"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

Simple question concerning VBA functions
 

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

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

Simple question concerning VBA functions
 

Great!

With -command solved the issue.

Thanks

Br
Vepa

"Vepa" wrote:

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