Link to home
Start Free TrialLog in
Avatar of John Carney
John CarneyFlag for United States of America

asked on

Converting a Sub to a Function

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

Thanks,
John
Avatar of Anthony Berenguel
Anthony Berenguel
Flag of United States of America image

gabrielPennyback,

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

thanks!

aebea
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.

thanks!

aebea
ASKER CERTIFIED SOLUTION
Avatar of Anthony Berenguel
Anthony Berenguel
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of John Carney

ASKER

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!
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.

Thanks,
John