Hi, I have searched the net for a solution but found non.
My goal is to write a function that I can use anywhere in a work book.
The function gets a project number (integer) & returns the last Bill number of the project as a string. this will save me using the DB functions (they are not subtable for my usage)
I am using excel 2010 (started this week) & the problem is that the function returns results that dont make sense. in order to check why the function doesnt work right I copied the code into a sub to run it step by step & debug it.
in the sub the results always where correct but the function resulted in other things.
the bills DB is built as so:
column A - project number
column B - bill date
column C - bill number as text (for example 0904B4)
my function:
Function LastBill(ProjectNumber As Integer) As String
Dim BillNumber As String
Dim NewestDate As Date
Dim i As Integer
Dim rngend As Long
Sheets(3).Activate 'this is the sheet with the bills BD. the function will always be on other sheets
rngend = LastRow()
NewestDate = "1/1/1900"
Range("A1").Select
For i = 1 To rngend
ActiveCell.Offset(1, 0).Select 'bill rows start from row 2
If ActiveCell.Value = ProjectNumber And NewestDate < ActiveCell.Offset(0, 2).Value Then 'checks if it is the project & if there is a newer bill (there are no two bills on the same day)
BillNumber = ActiveCell.Offset(0, 1).Value
NewestDate = ActiveCell.Offset(0, 2).Value
End If
Next i
LastBill = BillNumber
End Function
LastRow() is a function that returns the last row, it works perfectly & I use it in many other subs & functions.
I compared different variables such as lastrow & newestdate & the function always returns something wrong & the sub gets it right. There is also a difference in the result of the function in the cell to the result written in the function wizard.
for example when I returned the lastrow value (which was 74) the sub returned 74. the function wizard showed 45 & the cell had 0. for some reason the cell always shows 0. in some cells it even doesn't show the result, it shows the formula "=LasBill(A7)"
I have also tried adding Public before the function but it didn't change a thing.
Is it possible there is some new or old option that I am missing?
My goal is to write a function that I can use anywhere in a work book.
The function gets a project number (integer) & returns the last Bill number of the project as a string. this will save me using the DB functions (they are not subtable for my usage)
I am using excel 2010 (started this week) & the problem is that the function returns results that dont make sense. in order to check why the function doesnt work right I copied the code into a sub to run it step by step & debug it.
in the sub the results always where correct but the function resulted in other things.
the bills DB is built as so:
column A - project number
column B - bill date
column C - bill number as text (for example 0904B4)
my function:
Function LastBill(ProjectNumber As Integer) As String
Dim BillNumber As String
Dim NewestDate As Date
Dim i As Integer
Dim rngend As Long
Sheets(3).Activate 'this is the sheet with the bills BD. the function will always be on other sheets
rngend = LastRow()
NewestDate = "1/1/1900"
Range("A1").Select
For i = 1 To rngend
ActiveCell.Offset(1, 0).Select 'bill rows start from row 2
If ActiveCell.Value = ProjectNumber And NewestDate < ActiveCell.Offset(0, 2).Value Then 'checks if it is the project & if there is a newer bill (there are no two bills on the same day)
BillNumber = ActiveCell.Offset(0, 1).Value
NewestDate = ActiveCell.Offset(0, 2).Value
End If
Next i
LastBill = BillNumber
End Function
LastRow() is a function that returns the last row, it works perfectly & I use it in many other subs & functions.
I compared different variables such as lastrow & newestdate & the function always returns something wrong & the sub gets it right. There is also a difference in the result of the function in the cell to the result written in the function wizard.
for example when I returned the lastrow value (which was 74) the sub returned 74. the function wizard showed 45 & the cell had 0. for some reason the cell always shows 0. in some cells it even doesn't show the result, it shows the formula "=LasBill(A7)"
I have also tried adding Public before the function but it didn't change a thing.
Is it possible there is some new or old option that I am missing?
Last edited:
