MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Function still isn't working???


Posted by Tim on January 02, 2002 1:09 PM

I posted this message the other day and got a response that didn't work. The following is the code for my Function:

Function GetBalance(ID)

Sheets("ExportAmort").Activate
Set r = Sheets("ExportAmort").Range("g10").CurrentRegion
GetBalance = Application.WorksheetFunction.VLookup(ID, r, 6, 0)

End Function


If I set the range as g10:bb128, for example, instead of using CurrentReqion, it works. It is something with the CurrentRegion function that doesn't work. When I write a small procedure using the same range defintion, it selects the correct range. So, there is something wrong with the combination of Current Region in this Function.

Help!
Tim.


Posted by Damon Ostrander on January 02, 2002 1:54 PM

Hi Tim,

Your code looks fine. The only possible reason that I can see for your code not working is that CurrentRegion is yielding a range that you are not expecting. This can happen several ways:

1. your table is not surrounded by truly empty cells. Don't forget that even a cell that contains a blank (and thus looks empty) is not truly empty and CurrentRegion will have to expand to contain it.

2. your table contains an empty row or column. This will cause CurrentRegion to not include the rows or columns on the other side of the empty row or column.

3. the range of your table includes hidden rows or columns that are empty.

Here is a simple way to find out what is going wrong. Paste the following macro into a macro module:

Sub SelectCurrentRegion()
ActiveCell.CurrentRegion.Select
End Sub

Then tie a button or a shortcut keystroke to this macro so you can execute it easily.

Now just select a cell on your worksheet (start with G10) and run the macro. The CurrentRegion range will be selected so that you can now see what it is. I am sure you will find that either more than your table is getting selected, or only part of it.

Happy computing.

Damon

Posted by Ivan F Moala on January 02, 2002 8:26 PM

I think currentregion is one of those functions
that does not work within a function similar
to some specailcell types.
Heres a work around;

Set r = Range(Range("g10").End(xlToRight), Range("G10").End(xlDown))

As the current region is effectively oing the same.


Ivan