Cut down version of spreadsheet
Status Type
Col 4 Col 5
Row 6 Found A

Row 8 On Way A
Row 9 On Way G

Row 10 Bidding C
Row 11 Found B

I am trying to write a built in function which will look from one worksheet
to another, and return the number of occurences of a certain status against a
particular type.

I wrote the following function with a view to doing this, but all I get is a
VALUE error. I tried to use the Debug.Print but nothing appears in the
Immediate window when I get there and, infact, I can't look at the VBA window
until after the calculation has finished in the worksheet. I tried inserting
a breakpoint after the Debug.Print statement but even with that I don't get
anything displayed in the Immediate window. I am really having difficulty
with understanding how to use the Debug facility properly at all.

I have a thought that the basics of my problem is how I am calling INDIRECT
but I may be wrong in that too.

************

Function Type_Prospectives(fFR As Integer, fLR As Integer, fWSName As
String, _
fCAT As String, fTS As String) As Integer

Type_Prospectives = 0

Dim I As Integer
Dim IV1 As String
Dim IV2 As String
Dim TV1 As String
Dim TV2 As String

For I = fFR To fLR
IV1 = fWSName & "!R" & (Str(I)) & "C4"
IV2 = fWSName & "!R" & (Str(I)) & "C5"
TV1 = Application.WorksheetFunction.INDIRECT(IV1, False)
TV2 = Application.WorksheetFunction.INDIRECT(IV2, False)
Debug.Print I, IV1, IV2, TV1, TV2
If TV1 = fCAT Then
If TV2 = fTS Then
Type_Prospectives = Type_Prospectives + 1
End If
End If
Next I

************

Typical call to function (in full spreadsheet) reads as:
=Type_Prospectives(6,145,Inventory,"Found","A")

Three are three potential values of fCAT, and eleven of fTS, so I am looking
to call this 33 times in the full spreadsheet.

One other strange quirk that I would welcome insight on is the fact that
when I tried to have the function statement as a single line, VBA would not
even let me save - the line just went red and I could not proceed. When I
inserted the second line and used the underscore continuation character, it
was all OK, at least as regards the Function statement anyway.

Any advice welcome, please.

Best regards

Philip