• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 207
  • Last Modified:

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.
0
Vriaeliss
Asked:
Vriaeliss
  • 3
  • 2
  • 2
2 Solutions
 
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
 
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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

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

Featured Post

Upgrade your Question Security!

Add Premium security features to your question to ensure its privacy or anonymity. Learn more about your ability to control Question Security today.

  • 3
  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now