We need to install the UDF:
User Defined Functions (UDFs) are very easy to install and use:
1. ALT-F11 brings up the VBE window
2. ALT-I
ALT-M opens a fresh module
3. paste the stuff in and close the VBE window
If you save the workbook, the UDF will be saved with it.
To remove the UDF:
1. bring up the VBE window as above
2. clear the code out
3. close the VBE window
To use the UDF from Excel:
=Lvd(B57:AE57,B64:AE64)
To learn more about macros in general, see:
http://www.mvps.org/dmcritchie/excel/getstarted.htm
or
http://www.cpearson.com/excel/Writin...ionsInVBA.aspx
for specifics on UDFs
--
Gary''s Student - gsnu200776
"sony654" wrote:
Thanks Gary, but I think I did something wrong.
I entered =Lvd(B57:AE57,B64:AE64) into my worksheet and updated the cell
ranges. Received a #NAME? error. Was I supposed to do somthing with the
UDF? All I want to do is identify the last number populated in a series or
multiple ranges.
Thanks - Tom
--
Sony Luvy
"Gary''s Student" wrote:
The problem is that LOOKUP may be a one trick pony, happy with only a single
column or row. Check out this UDF:
Function Lvd(r1 As Range, r2 As Range) As Variant
Lvd = ""
Set rr = Union(r1, r2)
lc = 0
For Each r In rr
With r
If .Value 0 And .Column lc Then
Lvd = .Value
lc = .Column
End If
End With
Next
End Function
In the worksheet use as:
=Lvd(B57:AE57,B64:AE64)
It will return the right-most value in the pair of ranges. It will work
with either numbers or text.
--
Gary''s Student - gsnu200776
"sony654" wrote:
I am using the below function to return the last data value populated in the
range B57:AE57.
=IF(COUNT(B57:AE57),LOOKUP(99^99,B57:AE57),"-") ---- this works for a single
line of data
I want to be able to include multiple ranges in the look-up. Specifically,
I want to be able to include B64:AE64 in the look-up. I wrote it as follows
and it returned the N/A error message. Please, what am I doing wrong?
=IF(COUNT(B57:AE57,B64:AE64),LOOKUP(99^99,B57:AE57 ,B64:AE64),"-")----this
doesn't work for 2 lines of data
Thanks - Tom
--
Sony Luvy