Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
OFFSET behaving oddly
In the continuing vain effort solve the old "how can I concatenate an entire
range of cells" question, I have been trying to trick certain Excel functions by passing parameters that aren't strictly valid. In the process, I have come up with a fluke in the OFFSET function that I cannot explain and I thought perhaps one of you could shed some light. (Excel 2003, SP2). To reproduce this, enter the following values in the corresponding cells: A1: a A2: b A3: c In cell B1, enter this formula (NOT array-entered): =OFFSET($A$1,0,0,{3,2,1}) Now copy down to B2 ad B3. The results I get for B1, B2, B3 are... a, b, c And if I copy it down to cell B4 (or, more precisely, on any row other than 1, 2, or 3), I get #VALUE! Yes, I know the array is an invalid [height] argument, but still, interesting, no? Anybody have any thoughts on this? Ryan |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
OFFSET behaving oddly
Hi Ryan,
(Idea: http://www.mcgimpsey.com/excel/udfs/multicat.html, this one works also on arrays) Option Explicit '******************************************** 'Purpose: Concatenate all cells in a range or ' array 'Inputs: vP - range/array to be concatenated ' sDelim - optional delimiter to be ' inserted between text parts 'Returns: Concatenated string '***************************************** Function MultiCat( _ ByRef vP As Variant, _ Optional ByVal sDelim As String = "") _ As String Dim vE As Variant For Each vE In vP MultiCat = MultiCat & sDelim & vE Next vE MultiCat = Mid(MultiCat, Len(sDelim) + 1) End Function HTH, Bernd |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
OFFSET behaving oddly
Thanks Bernd,
I actually already have a similar concatenation udf I've written myself. I was just trying to come up with a way of tricking Excel into doing it without the help of a udf. Ryan " wrote: Hi Ryan, (Idea: http://www.mcgimpsey.com/excel/udfs/multicat.html, this one works also on arrays) Option Explicit '******************************************** 'Purpose: Concatenate all cells in a range or ' array 'Inputs: vP - range/array to be concatenated ' sDelim - optional delimiter to be ' inserted between text parts 'Returns: Concatenated string '***************************************** Function MultiCat( _ ByRef vP As Variant, _ Optional ByVal sDelim As String = "") _ As String Dim vE As Variant For Each vE In vP MultiCat = MultiCat & sDelim & vE Next vE MultiCat = Mid(MultiCat, Len(sDelim) + 1) End Function HTH, Bernd |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Offset Function | Excel Worksheet Functions | |||
Offset Function | Excel Worksheet Functions | |||
Offset function referencing worksheet | Excel Discussion (Misc queries) | |||
Offset Function works in cell, not in named range | Excel Worksheet Functions | |||
Question for use of offset and range | Excel Worksheet Functions |