creating a multidimensional array help

[mareharbor]

Can anyone help me to change this array to a multidimensiona l array with 1 more components i want to add another "column" to the array with a similiar specification of "cell.Offse t(0, -2).Value". So, actually want to add one more "column" as well, but it will contain another amount of similiar values of undetermined length as well. My Code is below, which also contains a section that puts the array into another sheet. Thank you all.

Public Sub DIFFREC()
Dim cell As Variant
Dim rw As Integer
Dim x As Integer
'Dim rwCt As Integer
'rwCt = Range("myRange").Count
Dim arrTicker() As String
rw = 1 ' array counter
For Each cell In Range("myRange")
	 If Abs(cell.Value) > 0 Then
		 ReDim Preserve arrTicker(rw)
		 arrTicker(rw - 1) = cell.Offset(0, -3).Value
		 rw = rw + 1
	 End If
Next cell
Windows("SENTRY.DIFF.05.07.xls").Activate
For rw = 1 To UBound(arrTicker)
	 x = x + 1
	 Cells(x + 2, 1).Value = arrTicker(rw - 1)
Next
End Sub

[danp129]

Public Sub DIFFREC()
    Dim cell As Variant
    Dim rw As Integer
    Dim x As Integer
    'Dim rwCt As Integer
    'rwCt = Range("myRange").Count
    Dim arrTicker() As String   'Declare arrTicker as string array
    rw = 0 ' array counter
    For Each cell In Range("myRange")
        If Abs(cell.Value) > 0 Then
            ReDim Preserve arrTicker(0 To 1, rw) '(re)Dimension arrTicker(columns, rows)
            arrTicker(0, rw) = cell.Offset(0, -3).Value
            arrTicker(1, rw) = cell.Offset(0, -2).Value
            rw = rw + 1
        End If
    Next cell
    Windows("SENTRY.DIFF.05.07.xls").Activate
    x = 0
    For rw = 0 To UBound(arrTicker, 2)  ', 2' refers to 2nd dimension
        x = x + 1
        Cells(x + 1, 1).Value = arrTicker(0, rw)
        Cells(x + 1, 2).Value = arrTicker(1, rw)
    Next
End Sub

Keep in mind when redimensioning a multi-dimension array you can only redimension the right most dimension.
This will work

ReDim Preserve arrTicker(0 To 1, mynewval)

This will not work

ReDim Preserve arrTicker(mynewval, 0 to 1)

You were also using a 0 based array but started from 1 and also were creating an array that was larger then what you currently needed. I changed it all to 0 based.

[SammyB]

Not too sure I understood your specs, but I would not bother with an array at all. The code below copies all of the positive cells in myRange which can be anywhere & have anynumber of columns. Positive cells are copied to the output workbook begining at A3. The output columns remain the same, but the output row is "collapsed" when the value is negative. If this is not what you want, let me know.

Sub DIFFREC()
	Dim rIn As Range
	Set rIn = Range("myRange")
	Dim wsOut As Worksheet
	Set wsOut = Windows("SENTRY.DIFF.05.07.xls").ActiveSheet
	Dim rOut As Range
	Set rOut = wsOut.Cells(3, 1)
	Dim iCol As Integer, iRow As Integer
	Dim i As Integer	' Output row offset
	For iCol = 1 To rIn.Columns.Count
		i = 0
		For iRow = 1 To rIn.Rows.Count
			If rIn.Cells(iRow, iCol).Value > 0 Then
				rOut.Offset(i, iCol - 1) = rIn.Cells(iRow, iCol)
				i = i + 1
			End If
		Next iRow
	Next iCol
End Sub