VBA Function won't work!
Can anybody please enlighten me why this Excel VBA function* works:
Function topright(values) topright = values.Offset(0, values.Columns.Count - 1) End Function and why this one does not (it alwasy returns a #VALUE! error): Function topright(values) topright = values.Offset(0, values.Columns.Count - 1) * 2 End Function Help, would greatly appreciated before insanity sets in. * this function, by the way, is supposed to find the top right hand value in any excel range. So if, in Excel, you did =topright(A1:F5), the function would return the value in A5. |
VBA Function won't work!
|
VBA Function won't work!
The first function is actually returning an array of the same
dimensions as the input range, with the top right cell of the input as its top left; so it just happens to display the right value. To fix the second function you need to make sure you pick the single cell you're interested in. The line: topright = values.Offset(0, values.Columns.Count - 1).Cells(1, 1).2 will do it. Andrew wrote: Can anybody please enlighten me why this Excel VBA function* works: Function topright(values) topright = values.Offset(0, values.Columns.Count - 1) End Function and why this one does not (it alwasy returns a #VALUE! error): Function topright(values) topright = values.Offset(0, values.Columns.Count - 1) * 2 End Function Help, would greatly appreciated before insanity sets in. * this function, by the way, is supposed to find the top right hand value in any excel range. So if, in Excel, you did =topright(A1:F5), the function would return the value in A5. |
VBA Function won't work!
Andrew - thank you v much - you hit the nail on th ehead (i did not
know it returned an array!). Piotr, thank you too for your input! - Chris Andrew Taylor wrote: The first function is actually returning an array of the same dimensions as the input range, with the top right cell of the input as its top left; so it just happens to display the right value. To fix the second function you need to make sure you pick the single cell you're interested in. The line: topright = values.Offset(0, values.Columns.Count - 1).Cells(1, 1).2 will do it. Andrew wrote: Can anybody please enlighten me why this Excel VBA function* works: Function topright(values) topright = values.Offset(0, values.Columns.Count - 1) End Function and why this one does not (it alwasy returns a #VALUE! error): Function topright(values) topright = values.Offset(0, values.Columns.Count - 1) * 2 End Function Help, would greatly appreciated before insanity sets in. * this function, by the way, is supposed to find the top right hand value in any excel range. So if, in Excel, you did =topright(A1:F5), the function would return the value in A5. |
VBA Function won't work!
Try:
Function topright(values As Range) As Double topright = values(1, values.Columns.Count) * 2 End Function The reason your function doesn't work is because the Offset method returns a range of cells and your function is tyring to multiply an array by a single element (i.e. 2). Regards, Greg " wrote: Can anybody please enlighten me why this Excel VBA function* works: Function topright(values) topright = values.Offset(0, values.Columns.Count - 1) End Function and why this one does not (it alwasy returns a #VALUE! error): Function topright(values) topright = values.Offset(0, values.Columns.Count - 1) * 2 End Function Help, would greatly appreciated before insanity sets in. * this function, by the way, is supposed to find the top right hand value in any excel range. So if, in Excel, you did =topright(A1:F5), the function would return the value in A5. |
VBA Function won't work!
On 29 Jun 2006 00:39:13 -0700, wrote:
Piotr - many many thanks for the quick reply. I understand what you are doing in your function (-checking for non numeric values), but that was not what I had meant to find out. Right. I've focused on wrong aspect of your code. Anyway, you got answer from AT. -- PL |
All times are GMT +1. The time now is 02:50 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com