Extending Look-up to multiple cell ranges

[sony654]

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

 

[Gary''s Student]

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.

 

[sony654]

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

 

[Gary''s Student]

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/WritingFunctionsInVBA.aspx
for specifics on UDFs

 

[sony654]

Gary - what do I paste in to the module? Thanks for your help.. Meaning,
paste what stuff in and close the VBE window? Below? And how do I run it?

=Lvd(B57:AE57,B64:AE64)