Function, User Defined
 

I've tried creating a user defined function that can look at all the sheets in a
workbook and find the number of occurrences of a particular string specified in
the function. The function, which would be used in a tally sheet, would look
something like this:

CountItems(B4, A3, "B1:B30","xyz")

B4 is the column label to match. That is, if the column label is "ABC", I want
the function to find the number of occurrences of my string "xyz" in the sheet
specified by A3.

B1:B30 is the range of column labels on each sheet to look at for the string
that matches B4.

"xyz" is the string I'm looking for.

Any help would be greatly appreciated.

I'd post what code I've tried, but I'm afraid of embarrassing myself. I've
tried using .Find to find the column and then COUNTIF to find the string, but
everything I've tried returns #VALUE.

Thanks.

Function, User Defined
 

Bill,

I found this post somewhat confusing. Can I seek clarification?

At one point you say all sheets, then you mention the sheet specified by A3.
A3 is a cell id, so what exactly do you mean?

I also got confused by the column labels for B4 and B1:B30. What exactly do
you mean by all of this?

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Bill Oertell" wrote in message
...
I've tried creating a user defined function that can look at all the
sheets in a
workbook and find the number of occurrences of a particular string
specified in
the function. The function, which would be used in a tally sheet, would
look
something like this:

CountItems(B4, A3, "B1:B30","xyz")

B4 is the column label to match. That is, if the column label is "ABC", I
want
the function to find the number of occurrences of my string "xyz" in the
sheet
specified by A3.

B1:B30 is the range of column labels on each sheet to look at for the
string
that matches B4.

"xyz" is the string I'm looking for.

Any help would be greatly appreciated.

I'd post what code I've tried, but I'm afraid of embarrassing myself.
I've
tried using .Find to find the column and then COUNTIF to find the string,
but
everything I've tried returns #VALUE.

Thanks.

Function, User Defined
 

I thought I had deleted the reference to all sheets. What I was thinking is
that I needed the function to look at all sheets and find the sheet with the
value referenced in A3. IOW, if A3 on the tally sheet contains the value ABC,
the function needs to find the sheet that has ABC in some cell, say B3. Then it
needs to find the column heading in that sheet that has the value passed to it
as the first variable, in my example B4. Then it needs to find the number of
occurrences of the last variable in the found column. The reference to B1:B30
is the range of column heading to look at. Maybe if I post the code I've got,
you can see what I'm trying to attempt:

Function COUNTITEMS(daaString As Variant, sht As Variant, rng As Range, ss As
String)
a = 1
Do While Worksheets(a)
'find sheet with sht in cell B3
If Application.Worksheets(a).Cells(3, 2) = sht Then Exit Do
a = a + 1
Loop

With Worksheets(1).Range(rng)
'find column with daaString in heading
Set c = .Find(daaString, LookIn:=xlValues)
If Not c Is Nothing Then
firstAddress = c.Address
'get column letter
nwRng = Left(c.Address, 2) & ":" & Left(c.Address, 2)
End If
'find number of occurrences of ss in found column and pass it to function
COUNTDAA = Application.WorksheetFunction.CountIf(nwRng, ss)
End With

End Function


"Bob Phillips" wrote in message
...
Bill,

I found this post somewhat confusing. Can I seek clarification?

At one point you say all sheets, then you mention the sheet specified by A3.
A3 is a cell id, so what exactly do you mean?

I also got confused by the column labels for B4 and B1:B30. What exactly do
you mean by all of this?

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Bill Oertell" wrote in message
...
I've tried creating a user defined function that can look at all the
sheets in a
workbook and find the number of occurrences of a particular string
specified in
the function. The function, which would be used in a tally sheet, would
look
something like this:

CountItems(B4, A3, "B1:B30","xyz")

B4 is the column label to match. That is, if the column label is "ABC", I
want
the function to find the number of occurrences of my string "xyz" in the
sheet
specified by A3.

B1:B30 is the range of column labels on each sheet to look at for the
string
that matches B4.

"xyz" is the string I'm looking for.

Any help would be greatly appreciated.

I'd post what code I've tried, but I'm afraid of embarrassing myself.
I've
tried using .Find to find the column and then COUNTIF to find the string,
but
everything I've tried returns #VALUE.

Thanks.