Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 395
Default 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
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Variant Trees/Lisp Like Lists in a single cell. John Creighton Excel Programming 0 February 13th 09 03:23 AM
Multi names in one cell copied to single cell ron_dallas Excel Programming 5 October 3rd 06 11:06 PM
Array problem: Key words-Variant Array, single-element, type mismatch error davidm Excel Programming 6 November 9th 05 05:54 AM
Array problem: Key words-Variant Array, single-element, type mismatch error davidm Excel Programming 1 November 8th 05 04:21 AM
variant array containing cel adresses convert to actual ranges-array Peter[_21_] Excel Programming 5 December 10th 03 09:50 PM


All times are GMT +1. The time now is 02:29 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"