Passing single-cell vs multi-cell ranges to a variant array?

K

ker_01

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top