Turning a Sub into a Function

[jonnyrivers]

I have this Sub that I get exactly what I want, but I want to make it a function so the the "Team" Variable can be a cell that I select. Here is the Sub code.
Option Explicit








Sub MYCOUNT()




Dim Team As String
Team = Range("BC7")




Dim rg2 As Range
Set rg2 = Sheets(1).Range("C5:AR5")




Dim Week As String
Week = Sheets("LMS").Range("BC6").Value




Dim c As Range
Set c = rg2.Find(What:=Week, MatchCase:=False)




Dim CountRange As Range
Set CountRange = Range(Cells(6, c.Column), Cells(600, c.Column))




Application.Volatile (True)


Dim Equation As Integer
Equation = Application.WorksheetFunction.CountIf(CountRange, Team)




MsgBox Equation




End Sub
<style type="text/css">p.p1 {margin: 0.0px 0.0px 0.0px 0.0px; font: 11.0px Menlo; color: #011993 ; background-color: #ffffff }p.p2 {margin: 0.0px 0.0px 0.0px 0.0px; font: 11.0px Menlo; background-color: #ffffff ; min-height: 13.0px}p.p3 {margin: 0.0px 0.0px 0.0px 0.0px; font: 11.0px Menlo; color: #000000 ; background-color: #ffffff }span.s1 {color: #000000 }span.s2 {color: #011993 }</style>

Everything works perfectly as the Sub. Here is the function code I came up with. All I am doing is making the Team variable something I can select on the sheet. All I have gotten is a #VALUE ! error in the cell.

<style type="text/css">p.p1 {margin: 0.0px 0.0px 0.0px 0.0px; font: 11.0px Menlo; color: #011993 ; background-color: #ffffff }p.p2 {margin: 0.0px 0.0px 0.0px 0.0px; font: 11.0px Menlo; background-color: #ffffff ; min-height: 13.0px}p.p3 {margin: 0.0px 0.0px 0.0px 0.0px; font: 11.0px Menlo; color: #000000 ; background-color: #ffffff }span.s1 {color: #000000 }span.s2 {color: #011993 }</style>
Option Explicit


Function MYCOUNT(Team As String) As Integer




Dim rg2 As Range
Set rg2 = Sheets(1).Range("C5:AR5")


Dim Week As String
Week = Sheets("LMS").Range("BC6").Value


Dim c As Range
Set c = rg2.Find(What:=Week, MatchCase:=False)


Dim CountRange As Range
Set CountRange = Range(Cells(6, c.Column), Cells(600, c.Column))




Application.Volatile (True)




Dim Equation As Integer
Equation = Application.WorksheetFunction.CountIf(CountRange, Team)




MYCOUNT = Equation




<style type="text/css">p.p1 {margin: 0.0px 0.0px 0.0px 0.0px; font: 11.0px Menlo; color: #011993 ; background-color: #ffffff }p.p2 {margin: 0.0px 0.0px 0.0px 0.0px; font: 11.0px Menlo; background-color: #ffffff ; min-height: 13.0px}p.p3 {margin: 0.0px 0.0px 0.0px 0.0px; font: 11.0px Menlo; color: #000000 ; background-color: #ffffff }span.s1 {color: #000000 }span.s2 {color: #011993 }</style>
End Function


I am about to pull my hair out over this function. Someone please HELP!!! Thanks in advance.
<style type="text/css">p.p1 {margin: 0.0px 0.0px 0.0px 0.0px; font: 11.0px Menlo; color: #011993 ; background-color: #ffffff }p.p2 {margin: 0.0px 0.0px 0.0px 0.0px; font: 11.0px Menlo; background-color: #ffffff ; min-height: 13.0px}p.p3 {margin: 0.0px 0.0px 0.0px 0.0px; font: 11.0px Menlo; color: #000000 ; background-color: #ffffff }span.s1 {color: #000000 }span.s2 {color: #011993 }</style>

 

[Rick Rothstein]

Show us the formula you are using to call your function.

 

[jonnyrivers]

=mycount(a1)

 

[Rick Rothstein]

=mycount(a1)

Are you putting quote marks around the value you type in cell A1 by any chance?

 

[jonnyrivers]

No, it is just text.

 

[Rick Rothstein]

No, it is just text.

Next try.:wink: What sheet is the active sheet? This line of code refers to it...

Set CountRange = Range(Cells(6, c.Column), Cells(600, c.Column))

but it looks like Column C should be referring to Sheet(1) based on a previous line of code. Maybe change this line of code to this?

Set CountRange = Sheet(1).Range(Sheet(1).Cells(6, c.Column), Sheet(1).Cells(600, c.Column))

 

[jonnyrivers]

I am still getting the #VALUE ! error. In the original Sub code I get the value I am looking for with the MsgBox.

 

[Rick Rothstein]

I am still getting the #VALUE ! error. In the original Sub code I get the value I am looking for with the MsgBox.

Hmm, I do not see an obvious problem with your function, but I also do not have any real data to test it against. Any chance you can post a copy of the workbook to a file sharing facility such as DropBox so that we can debug the code live against actual data?

 

[jonnyrivers]

Here is the workbook. I am putting the function in column BD and it is referencing the cells in column BC. Function should be counting the teams in column F.
https://www.dropbox.com/s/ebzqoef6fy56mzr/MrExcelTest.xlsm?dl=0

 

[jonnyrivers]

Hmm, I do not see an obvious problem with your function, but I also do not have any real data to test it against. Any chance you can post a copy of the workbook to a file sharing facility such as DropBox so that we can debug the code live against actual data?

Were you able to open the file?