Passing single-cell vs multi-cell ranges to a variant array?
I have a function that pulls in a range [e.g. Function XYZ (TempRange as
range)] I then assign that range to a class module's variant array: abc.SourceDataRange = TempRange.Value Then I perform various calculations on SourceDataRange within that class module. I'm testing a few unlikely (but still potential) scenarios, and I found a problem that I don't know how to fix. Under almost all cases, more than one cell will be selected. My later code loops through the values using references like SourceDataRange(i,1). However, when a single cell is passed through the function, abc.SourceDataRange = TempRange.Value returns that single cell's value, instead of an array- so my class module code can't process it when it hits the first line coded with SourceDataRange(i,1), because SourceDataRange is a single value instead of an array. I can determine when the reference is a single cell in the function, using [TempRange.Cells.Count], so I tried the following to try to force the single value into an array format for my class module: If TempRange.Cells.Count 1 Then abc.SourceDataRange = TempRange.Value Else abc.SourceDataRange = Array(TempRange.Value) End If Which pushes the value over into SourceDataRange(0)... so it is in an array, but still not to the point where I can reference it as as SourceDataRange(1,1). Any ideas on syntax to pass the single value over so that it will work the same as if a 2+ cell range had been selected? Thank you! Keith |
All times are GMT +1. The time now is 11:49 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com