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]

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

I cannot test things fully here because your worksheet has references to other workbooks that are located on your local hard drive. However, I think the problem might be located in the Worksheet_Change event you have on the LMS sheet. Just guessing here, but I think it may be failing and the On Error Resume Next statement may be hiding it from you. Try commenting that line out, saving the workbook, closing it and then re-opening it. Where I think the problem may be in the Application.Goto statement. You have the "reference" equalling a variable named "cell"; however, I don't see any place where you set a reference to it, so it is empty. I do note that if you select cell BD7, click into the Formula Bar and then hit the Enter Key, the formula evaluates (and if you copy it down, all of the cells update with a value). So, I think execution during load is stop in your Worksheet_Change event so that the updating of the formula cells never takes place. Hopefully all of this will give you some guidance if figuring out how to get it all working as it should.