Works in a Sub, but Fails as a Function. Anyone know why?

So, This works fine. If there are 194 rows used, then it returns 194:
Sub ShowMeLastRow()
 Dim lLastRow As Long
 ' Define the number of the bottom row
 lLastRow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
 ' Send the last row's number back
 MsgBox lLastRow
End Sub

However, this just gives me an error:
Sub ShowMeLastRow()
 MsgBox fLastRowNumber
End Sub
Function fLastRowNumber()
 Dim lLastRow As Long
 ' Define the number of the bottom row
 lLastRow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
 ' Send the last row's number back
 LastRowNumber = lLastRow
End Function

The error I get is:
Run-time error '1004':
Method 'Range' of object '_Global' failed

Probably something stupid, but my brain's fried as is, so I'm not seeing it :(

Thanks in advance.
VriaelissAsked:
Who is Participating?
 
g_johnsonCommented:
this worked fine for me:


Private Sub CommandButton1_Click()

    Dim sStop As String
    ' Fill down the Column A formula "ProgInfo"
    Range("A2").Copy Range("A5")
    Application.CutCopyMode = False
    sStop = "A5:A" & fLastRowNumber
    Range("A5").AutoFill Destination:=Range(sStop) '****Error happens here****


End Sub
Function fLastRowNumber() As Long
 Dim lLastRow As Long
 ' Define the number of the bottom row
 lLastRow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
 ' Send the last row's number back
 fLastRowNumber = lLastRow
End Function

0
 
g_johnsonCommented:
function fLastRowNumber() as Long
0
 
VriaelissAuthor Commented:
Thanks for the quick response. It fixed part of the problem, but I'm still getting that error in this code specifically, even with that change:

Sub FillOutFormulae()
    Dim sStop As String
    ' Fill down the Column A formula "ProgInfo"
    Range("A2").Copy Range("A5")
    Application.CutCopyMode = False
    sStop = "A5:A" & fLastRowNumber
    Range("A5").AutoFill Destination:=Range(sStop)****Error happens here****
...
End Sub

0
Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

 
g_johnsonCommented:
so this is a different question now?
I'm assuming this is Excel VBA -- i'll see if I can figure out the syntax for that line.
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
As g_johnson commented above, you have a function named fLastRowNumber, and in the code you are setting {hey looky here, no 'f'}LastRowNumber = lLastRow

0
 
VriaelissAuthor Commented:
I meant to split points... getting mods to re-open question so I can :)

Thanks to both of you. It works now. (Gotta love typos xP)
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Thanks for the split.  Good luck with your project.  -Jim
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.