Help! my VBA function doesnt result right

ophirw

New Member
Joined
Nov 23, 2010
Messages
6
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?
 
Last edited:

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)
without example data to see how the function would work, it is hard to say. I will guess though that it is
ActiveCell.Offset(1, 0).Select --> ActiveCell.Offset(i, 0).Select

you should also look into using ranges as well vs. using select alot
 
Upvote 0
I will try posting a file with some numbers.

if you write activecell.offset(i, 0).select instead the for loop instead of jumping 1 row at a time it will jump in i increments.

I want it to go row by row
 
Upvote 0
erh?

if it is 1, then you will not move row by row.

the for i=1 to rngend will increase 1 by 1 each time until it gets to rngend and should therefore go row by row....

give it a try
 
Upvote 0
I wrote the bill DB wrong.
the bills DB is built as so:
column A - project number
column B - bill number as text (for example 0904B4)
column C - bill date

I do not know how to load a file so here is a small example:
sheet 1:
<table dir="RTL" border="0" cellpadding="0" cellspacing="0" width="204"><col style="width: 80pt;" width="106"> <col style="width: 74pt;" width="98"> <tbody><tr style="height: 14.25pt;" height="19"> <td class="xl67" dir="LTR" style="height: 14.25pt; width: 80pt;" width="106" height="19">A</td> <td class="xl68" dir="LTR" style="width: 74pt;" width="98">B</td> </tr> <tr style="height: 14.25pt;" height="19"> <td class="xl65" dir="LTR" style="height: 14.25pt;" align="left" height="19">project number</td> <td dir="LTR" align="left">last bill issued</td> </tr> <tr style="height: 14.25pt;" height="19"> <td class="xl66" dir="RTL" style="height: 14.25pt;" height="19">1006</td> <td dir="LTR" align="left">=LasBill(A2)</td> </tr> <tr style="height: 14.25pt;" height="19"> <td class="xl66" dir="RTL" style="height: 14.25pt;" height="19">914</td> <td dir="LTR" align="left">6</td> </tr> <tr style="height: 14.25pt;" height="19"> <td class="xl66" dir="RTL" style="height: 14.25pt;" height="19">912</td> <td dir="LTR" align="left">6</td> </tr> <tr style="height: 14.25pt;" height="19"> <td class="xl66" dir="RTL" style="height: 14.25pt;" height="19">904</td> <td dir="LTR" align="left">6</td> </tr> <tr style="height: 14.25pt;" height="19"> <td class="xl66" dir="RTL" style="height: 14.25pt;" height="19">903</td> <td dir="LTR" align="left">6</td> </tr> </tbody></table>

sheet 3:
<table style="width: 398px; height: 376px;" dir="RTL" border="0" cellpadding="0" cellspacing="0"><col style="width: 80pt;" width="106"> <col style="width: 42pt;" width="56"> <col style="width: 59pt;" width="78"> <col style="width: 66pt;" width="88"> <tbody><tr style="height: 14.25pt;" height="19"> <td dir="LTR" style="height: 14.25pt; width: 80pt;" align="left" width="106" height="19">A</td> <td dir="LTR" style="width: 42pt;" align="left" width="56">B
</td> <td dir="LTR" style="width: 59pt;" align="left" width="78">C</td> <td dir="LTR" style="width: 66pt;" align="left" width="88">D</td> </tr> <tr style="height: 14.25pt;" height="19"> <td dir="LTR" style="height: 14.25pt;" align="left" height="19">project number</td> <td dir="LTR" align="left">bill number</td> <td dir="LTR" align="left">bill date</td> <td dir="LTR" align="left">amount</td> </tr> <tr style="height: 14.25pt;" height="19"> <td class="xl65" dir="RTL" style="height: 14.25pt;" height="19">1006</td> <td class="xl65" dir="RTL">1006B1</td> <td class="xl66" dir="RTL">01/08/2010</td> <td class="xl67" dir="LTR"> $40,000.00 </td> </tr> <tr style="height: 14.25pt;" height="19"> <td class="xl65" dir="RTL" style="height: 14.25pt;" height="19">1006</td> <td class="xl65" dir="RTL">1006B2</td> <td class="xl66" dir="RTL">01/11/2010</td> <td class="xl67" dir="LTR"> $30,000.00 </td> </tr> <tr style="height: 14.25pt;" height="19"> <td class="xl65" dir="RTL" style="height: 14.25pt;" height="19">914</td> <td class="xl65" dir="RTL">0914B1</td> <td class="xl66" dir="RTL">01/04/2010</td> <td class="xl67" dir="LTR"> $ 4,310.34 </td> </tr> <tr style="height: 14.25pt;" height="19"> <td class="xl65" dir="RTL" style="height: 14.25pt;" height="19">914</td> <td class="xl65" dir="RTL">0914B2</td> <td class="xl66" dir="RTL">01/09/2010</td> <td class="xl67" dir="LTR"> $ 5,172.41 </td> </tr> <tr style="height: 14.25pt;" height="19"> <td class="xl65" dir="RTL" style="height: 14.25pt;" height="19">912</td> <td class="xl65" dir="RTL">0912B1</td> <td class="xl66" dir="RTL">09/11/2009</td> <td class="xl67" dir="LTR"> $ 3,678.30 </td> </tr> <tr style="height: 14.25pt;" height="19"> <td class="xl65" dir="RTL" style="height: 14.25pt;" height="19">904</td> <td class="xl65" dir="RTL">0904B4</td> <td class="xl66" dir="RTL">14/12/2010</td> <td class="xl67" dir="LTR"> $88,464.50 </td> </tr> <tr style="height: 14.25pt;" height="19"> <td class="xl65" dir="RTL" style="height: 14.25pt;" height="19">903</td> <td class="xl65" dir="RTL">0903B1</td> <td class="xl66" dir="RTL">01/03/2009</td> <td class="xl67" dir="LTR"> $ 4,329.00 </td> </tr> <tr style="height: 14.25pt;" height="19"> <td class="xl65" dir="RTL" style="height: 14.25pt;" height="19">903</td> <td class="xl65" dir="RTL">0903B2</td> <td class="xl66" dir="RTL">01/04/2009</td> <td class="xl67" dir="LTR"> $ 4,329.00 </td> </tr> <tr style="height: 14.25pt;" height="19"> <td class="xl65" dir="RTL" style="height: 14.25pt;" height="19">903</td> <td class="xl65" dir="RTL">0903B3</td> <td class="xl66" dir="RTL">01/05/2009</td> <td class="xl67" dir="LTR"> $ 4,329.00 </td> </tr> <tr style="height: 14.25pt;" height="19"> <td class="xl65" dir="RTL" style="height: 14.25pt;" height="19">903</td> <td class="xl65" dir="RTL">0903B4</td> <td class="xl66" dir="RTL">01/06/2009</td> <td class="xl67" dir="LTR"> $ 4,329.00 </td> </tr> </tbody></table>
 
Upvote 0
As far as I know the offset.select function selects a cell in an offset of as much as you write it to in compare to the current active cell.

If the active cell is A2: offset(1, 0).select --> A3
offset(5, 0).select --> A7

& since the i grows the offset wont be of one row at a time.

you are probably thinking of range("A" & i).select

this worked fine in a sub, try it your self.
 
Last edited:
Upvote 0
If you're using this as a worksheet function, it won't work -- you can't do Select in a UDF.

Also, your UDF should receive as arguments all of the data it needs, rather than be sniffing other cells. Otherwise, Excel see no dependencies, and won't cause it to recalculate when the data changes.
 
Upvote 0
I moved the function to the personal.xlsb file, used it from the user defined functions & gave it also the sheet argument:
Public Function LastBill(ProjectNumber As Integer, sheetnumber As Integer) As String

but that is all the information i can give it. the whole idea is for the function to go look for that last bill of that project number in the DB that is on another sheet.

after this changes the results where exactly the same
 
Upvote 0

Forum statistics

Threads
1,223,099
Messages
6,170,107
Members
452,302
Latest member
TaMere

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top