Can anyone help me to change this array to a multidimensional array with 1 more components i want to add another "column" to the array with a similiar specification of "cell.Offset(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
-
2 1625 - 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.
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
-
Sign in to post your reply or Sign up for a free account.
Similar topics
by: TLOlczyk |
last post by:
I have a brain cramp and I need some help.
I have a chunk of code below which demonstrates
a problem I have with multidimensional arrays.
I want to keep it simple but something specific is...
|
by: TheKeith |
last post by:
I can't figure out why this doesn't work:
---------------------------------------------
greeting = new Array();
greeting = "hey";
greeting = "bye";
trace(greeting + greeting);
|
by: @ nospam.com |
last post by:
Hi,
If Im using Jagged arrays, the following is possible:
int arr = new int
for(int i=0; i<10; i++) {
arr = new int;
}
|
by: andy.mcvicker |
last post by:
Hi Gang
I have a large VB program that at one point does a lookup to a small
table (26 rows by 3 columns). With this table I have to do some
counting and retrieval of data. I'm finding that...
|
by: |
last post by:
I'm fairly new to ASP and must admit its proving a lot more unnecessarily
complicated than the other languages I know. I feel this is because there
aren't many good official resources out there to...
|
by: Bigshot |
last post by:
Im trying to scan a file using fscanf and want to put the results in a multidimensional array (since C has no strings I need it to store names).
Basically I want to be able to have a...
|
by: nitinm |
last post by:
hi
I want to make a program whose requirement are as following:
1) it has to create an NxN matrix after reading input (i.e. N) from a
file in the main() itself.
2) it has to send the array as...
|
by: LittleCake |
last post by:
Hi All,
I have a multidimensional array where each sub-array contains just two
entries, which indicates a relationship between those two entries. for
example the first sub-array:
=Array
(
=30...
|
by: Slain |
last post by:
I need to convert a an array to a multidimensional one. Since I need
to wrok with existing code, I need to modify a declaration which looks
like this
In the .h file
int *x;
in a initialize...
|
by: DolphinDB |
last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation.
Take...
|
by: DolphinDB |
last post by:
Tired of spending countless mintues downsampling your data? Look no further!
In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
|
by: ryjfgjl |
last post by:
ExcelToDatabase: batch import excel into database automatically...
|
by: isladogs |
last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM).
In this month's session, we are pleased to welcome back...
|
by: isladogs |
last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM).
In this month's session, we are pleased to welcome back...
|
by: jfyes |
last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
|
by: Defcon1945 |
last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
|
by: Shællîpôpï 09 |
last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
|
by: af34tf |
last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
| |