PDA

View Full Version : Arrays and range of noncontiguous cells



bigdoggit
08-02-2006, 09:24 AM
Okay, here's something that has me stumped. Is it even possible to pass a range of noncontiguous cells to an array. Won't work the way I'm used to when passing a contiguous range:

MasterDateArray = Worksheets("MasterSheet").Range("MasterDate").Resize(2500, 1)

where MasterDateArray, MasterSheet, and MasterDate are names I created

or using

MasterDateArray = Worksheets("MasterSheet").Range("A8:A15")

or using

MasterDateArray = Worksheets("MasterSheet").Range("A8:A15").Value


MasterDateArray is declared as a variant variable. Would love a solution. Greate possibilities if there is one. What do you all think?

OBP
08-02-2006, 01:09 PM
If you know the location of the cells then there must be a solution.
You may not be able to do it with a single instruction though it will probably take some kind of loop.
Any chance of attaching an example of what you want to do?
This post may contain a key to want you want to do -
http://vbaexpress.com/forum/showthread.php?t=9038

Bob Phillips
08-02-2006, 02:46 PM
Here is one way



Sub TestJoinArrays()
Dim myArray
myArray = JoinArrays(Application.Transpose(Range("A1:A3")), Application.Transpose(Range("B1:B3")))
End Sub

Public Function JoinArrays(ParamArray ary())
Dim i As Long
Dim sValues
Dim aryResult

For i = LBound(ary) To UBound(ary)
sValues = sValues & Join(ary(i), ",") & ","
Next i
aryResult = Split(sValues, ",")
ReDim Preserve aryResult(LBound(aryResult) To UBound(aryResult) - 1)
JoinArrays = aryResult
End Function

bigdoggit
08-03-2006, 08:37 AM
The worksheet contains financial information, and all zero values have a dash, - , in their place. This is intended to make isolating the cells with payments and expenses easier.

The procedure I'm talking about would use the special cells method to isolate all cells with constants that are numbers. Then it would pass that range to an array so it could be analyzed.

The analyzation would be sorting each entry according to its date. Entries would be sorted by month/year.

Now I didn't include any code with this sampel sheet, because I haven't got what I'm talking about working anyway. If someone finds a fast way to pass the noncontiguous range to an array, that is everything in this case.

Now, I'm doing this for speed, because at first I had zeroes, not dashes. But when I reach around 500 receipts, things start to get slow, and I hate slow. I want my procedures to operate in under a second. This one is already pushing past that, and I know I'll accumulate more than 500 receipts! Hope this is something more to play with.

As for the other thread, I have a way to connect the cells as one range, I just need to pass it to an array. That is my only sticking point.

Thanx xld for the code. I will continue to play with it. It does move two noncontiguous ranges into an array. What I need though is for it to be able to dynamically find these separate sections of the range. See the above description. It might be the hint I needed. Any more info would still be appreciated.