We help IT Professionals succeed at work.

Converting a Sub to a Function

John Carney
John Carney asked
Last Modified: 2012-02-14
How do I convert this sub into a function, and what would I then enter in the cells in Column AF? In other words, the formula in AF144 would be: =getSeatCounts(?)

The values I want to return are in Column EH.

Sub getSeatCounts()
Dim cel As Range
For Each cel In [AF11:AF144]
    If InStr(UCase(cells(cel.Row, [T1].Column)), "ROW") > 0 Then cel = cells(cel.Row, [StCnt_J].Column).Value
Next cel
End Sub

Watch Question


I'm confused by your question. Why doesn't you're existing subroutine work for you?


Ok, so it looks like for each row in your range [AF11:AF144], you're check to see if column [T1] contains "row", and if it does then you want the value of EH to populate in AF.

What columns do [T1] and [StCnt_J] represent? i.e. column A, column Q, etc.


This one is on us!
(Get your first solution completely free - no credit card required)
John CarneyReliability Business Tools Analyst II


Thanks for posting so fast, aebea.

The function formulas will go in Column AF.
The column being searched (for the word "row") is Column T.
And the values I want returned in Column AF are in Column EH.
Cool! The formula I posted above should do the trick for you then. Let me know if you have any questions.

Good luck!
John CarneyReliability Business Tools Analyst II


Thanks, aebea, this solves my immediate problem. If you know how to do this with a called function, I'd love to be able to do it that way in the future. I can't figure out how to write the function, or how to properly call it.


Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.


Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.