Using a user defined functoin to scan data for a number
 

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

Using a user defined functoin to scan data for a number
 

See replies in .Functions

Biff

"CSUS_CE_Student" wrote in
message ...
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

Using a user defined functoin to scan data for a number
 

Just use this formula

=LOOKUP(2,1/(G1:G45<""),G1:G45)

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)



"CSUS_CE_Student" wrote in
message ...
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

Using a user defined functoin to scan data for a number
 

An easier way than UDF for you, maybe:
=INDIRECT("G"&COUNTIF(G2:G45,"0")+1)
counts balances in column G that are above zero, since apparently you have
zeros following your last actual balance. This formula assumes that the
balance range starts in row 2 and is continuous - and there fore adds one to
obtain the actual row number of the latest balance. Adjust as needed.

"CSUS_CE_Student" wrote:

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