P
Philip Mark Hunt
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
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