Problem with a UDF im trying to write

[Guest]

I am trying to create a check register type spreadsheet, and wanting to use a
UDF,(or if you know another that will work too) to automaticly find the last
balance in the balance colum and return it in the cell at the bottom of the
page. Heres what im trying but it keeps locking up excel when i try to enter
it. I dont know if its the loop or the lack of an argument in the function
line.

Function EOMBal()
Dim a As Single
Range("G45").Select
a = 0
Do While a = 0
If a = 0 Then
ActiveCell.Offset(-1, 0).Select
End If
a = ActiveCell.Value
Loop
EOMBal = a
End Function

Im new to using functions and subs, and new to programing in general so any
advice would be appreaciated.
Thanks

 

[Guest]

This code works for me just fine, how are you calling it? What do you mean by
"when i try to enter it"

 

[T. Valko]

You don't need a UDF to do this:

Assume column F is the balance column.

To return the *LAST* numeric value from that column:

=LOOKUP(10^99,F:F)

Biff

 

[Dave Peterson]

First, use Biff's suggestion. You'll be happier--it's quicker and easier to
implement.

But you have some problems in your code (a learning exercise only!).

You'd want to pass the range to look at to your function. Otherwise, excel
won't know when to calculate. (You could make the function calculate whenever
excel calculated, but your results could be one calculation behind.)

You can't select cells in a UDF called from a worksheet cell.

But something like this (again, don't use it) worked ok for me:

Option Explicit
Function EOMBal(myCol As Range)
Dim myVal As Double
With myCol.Columns(1).EntireColumn
myVal = .Cells(.Rows.Count, 1).End(xlUp).Value
End With
EOMBal = myVal
End Function

And you use it in a cell like:
=eombal(G:G)

======
But I would change Biff's suggestion:
=LOOKUP(10^99,F:F)
to
=LOOKUP(10^99,G:G)

<vbg>