Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Converting range to variant is easy; how do I handle rows vs colum
I've created some code for myself that works pretty well. I input two
(vertical) ranges of data via a UDF that are then processed and modify my sheet as desired. Here is a simplified example of the piece of code I need to tweak Dim RangeToArray as Variant RangeToArray = Sheet1.Range("A1:A4").value 'Actual range is entered via a UDF, so it could be anywhere- this is just an example for i = lbound(RangeToArray) to ubound(RangeToArray) TempCellValue = RangeToArray(i,1) 'do stuff Next Now I need to share it with other users, and I want the code to handle different range entries without blowing up. Situation: User places data in a horizontal range instead of a vertical range My Approach: Check ubound of the range. If =1, then either it is a horizontal range, or there is only one data point. Loop the horizontal range and pull the data values out into a format that is compatible with the rest of my code. Problem: In my testing, I haven't been able to figure out how to find the max number of [horizontal] elements "i" in RangeToArray(1,i) [note, i is now the second parameter for a horizontal array] Ubound doesn't seem to work on this; I tried variations like ubound(RangeToArray(2)). Should I just make an obscenely high fixed loop (i= 1 to 1000000) and just catch the out of bounds error when it occurs, or is there a better way to find the width of a horizontal range that has been passed to a variant? Thank you! Keith |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Converting range to variant is easy; how do I handle rows vs colum
Ubound(RangeToArray)
OR Ubound(RangeToArray,1) will return the upper bound of the first dimension Ubound(RangeToArray,2) will return the upper bound of the second dimension If this post helps click Yes --------------- Jacob Skaria "ker_01" wrote: I've created some code for myself that works pretty well. I input two (vertical) ranges of data via a UDF that are then processed and modify my sheet as desired. Here is a simplified example of the piece of code I need to tweak Dim RangeToArray as Variant RangeToArray = Sheet1.Range("A1:A4").value 'Actual range is entered via a UDF, so it could be anywhere- this is just an example for i = lbound(RangeToArray) to ubound(RangeToArray) TempCellValue = RangeToArray(i,1) 'do stuff Next Now I need to share it with other users, and I want the code to handle different range entries without blowing up. Situation: User places data in a horizontal range instead of a vertical range My Approach: Check ubound of the range. If =1, then either it is a horizontal range, or there is only one data point. Loop the horizontal range and pull the data values out into a format that is compatible with the rest of my code. Problem: In my testing, I haven't been able to figure out how to find the max number of [horizontal] elements "i" in RangeToArray(1,i) [note, i is now the second parameter for a horizontal array] Ubound doesn't seem to work on this; I tried variations like ubound(RangeToArray(2)). Should I just make an obscenely high fixed loop (i= 1 to 1000000) and just catch the out of bounds error when it occurs, or is there a better way to find the width of a horizontal range that has been passed to a variant? Thank you! Keith |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
range and variant | Excel Programming | |||
How to work with only one colum of a variant | Excel Programming | |||
Converting equations into easy-to-understand words?! | Excel Discussion (Misc queries) | |||
how do I converting and handle files | Excel Discussion (Misc queries) | |||
Converting worksheets to workbooks. Is there an easy way? | Excel Discussion (Misc queries) |