Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Array/Matrix
I need help setting up an array to read data into.
I would like to read data from 3 columns on one sheet into an array and then copy that information into another sheet. The Array/Matrix will be 3 columns wide, but the number of rows will need to be dynamic. Can anyone help? TIA E |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Array/Matrix
If you want to copy and paste, you should just use the copy and paste
methods, and not worry about matrices. But if you need a dynamic matrix, I think you should look into "Redim Preserve". Redim will "Dim" the variable with new subscripts. For example, Dim var (10) as long [Use var in code. Decide that you need more than 10 long words] Redim var (20) as long The Redim has changed the size of the array. Unfortunately, it has also lost the 10 values in the previous array. To save these use the "Preserve" keyword: Dim var (10) as long [Use var in code. Decide that you need more than 10 long words] Redim Preserve var (20) as long Now, var has changed size, and the first 10 values remain unchanged. The order of the subscripts is important in a matrix. If you are going to change a matrix, change only the last subscript. For example: Dim Mat (10, 5) as long [Use var in code. Decide that you need more than 10 long words] Redim Preserve Mat (10, 10) as long Hope this helps, Dom Egon wrote: I need help setting up an array to read data into. I would like to read data from 3 columns on one sheet into an array and then copy that information into another sheet. The Array/Matrix will be 3 columns wide, but the number of rows will need to be dynamic. Can anyone help? TIA E |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Array/Matrix
Hi
Here is some code.. However you can accomplish this more succintly by just copying and pasting in code. Public Function CopyFromOneSheetToAnother(ByVal sOne$, ByVal sOther$) As Boolean Dim aValues() As Variant Dim i%, lNumRows& Dim wsOne As Worksheet Dim wsOther As Worksheet Set wsOne = ThisWorkbook.Worksheets(sOne) Set wsOther = ThisWorkbook.Worksheets(sOther) lNumRows = wsOne.Cells.SpecialCells(xlCellTypeLastCell).Row ReDim aValues(1 To lNumRows, 1 To 3) For i = 1 To wsOne.Cells.SpecialCells(xlCellTypeLastCell).Row aValues(i, 1) = wsOne.Cells(i, 1).Value aValues(i, 2) = wsOne.Cells(i, 2).Value aValues(i, 3) = wsOne.Cells(i, 3).Value Next i With wsOther With .Range(.Cells(1, 1), .Cells(lNumRows, 3)) .Value = aValues End With End With End Function "Egon" wrote: I need help setting up an array to read data into. I would like to read data from 3 columns on one sheet into an array and then copy that information into another sheet. The Array/Matrix will be 3 columns wide, but the number of rows will need to be dynamic. Can anyone help? TIA E |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Array/Matrix
Are you saying I sould just copy and paste on a per line basis between
the two workbooks and that it would be faster to do so? Alok wrote: Hi Here is some code.. However you can accomplish this more succintly by just copying and pasting in code. Public Function CopyFromOneSheetToAnother(ByVal sOne$, ByVal sOther$) As Boolean Dim aValues() As Variant Dim i%, lNumRows& Dim wsOne As Worksheet Dim wsOther As Worksheet Set wsOne = ThisWorkbook.Worksheets(sOne) Set wsOther = ThisWorkbook.Worksheets(sOther) lNumRows = wsOne.Cells.SpecialCells(xlCellTypeLastCell).Row ReDim aValues(1 To lNumRows, 1 To 3) For i = 1 To wsOne.Cells.SpecialCells(xlCellTypeLastCell).Row aValues(i, 1) = wsOne.Cells(i, 1).Value aValues(i, 2) = wsOne.Cells(i, 2).Value aValues(i, 3) = wsOne.Cells(i, 3).Value Next i With wsOther With .Range(.Cells(1, 1), .Cells(lNumRows, 3)) .Value = aValues End With End With End Function "Egon" wrote: I need help setting up an array to read data into. I would like to read data from 3 columns on one sheet into an array and then copy that information into another sheet. The Array/Matrix will be 3 columns wide, but the number of rows will need to be dynamic. Can anyone help? TIA E |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Array/Matrix
Hi Egon,
No copying and pasting on a per line basis is always the slower option. Your best bet is to copy information from various parts of the workbook and moving it into an array and then pasting it all at once to the other worksheet. The example I gave you should work except for the following 1. You will need to modify the routine to identify each row. 2. You will need to set up the array as a column by row array so that you can use Redim Preserve the existing information while you are adding additional rows in the second dimension. 3. Finally you will need to transpose the array as you post it in the other sheet. "Egon" wrote: Are you saying I sould just copy and paste on a per line basis between the two workbooks and that it would be faster to do so? Alok wrote: Hi Here is some code.. However you can accomplish this more succintly by just copying and pasting in code. Public Function CopyFromOneSheetToAnother(ByVal sOne$, ByVal sOther$) As Boolean Dim aValues() As Variant Dim i%, lNumRows& Dim wsOne As Worksheet Dim wsOther As Worksheet Set wsOne = ThisWorkbook.Worksheets(sOne) Set wsOther = ThisWorkbook.Worksheets(sOther) lNumRows = wsOne.Cells.SpecialCells(xlCellTypeLastCell).Row ReDim aValues(1 To lNumRows, 1 To 3) For i = 1 To wsOne.Cells.SpecialCells(xlCellTypeLastCell).Row aValues(i, 1) = wsOne.Cells(i, 1).Value aValues(i, 2) = wsOne.Cells(i, 2).Value aValues(i, 3) = wsOne.Cells(i, 3).Value Next i With wsOther With .Range(.Cells(1, 1), .Cells(lNumRows, 3)) .Value = aValues End With End With End Function "Egon" wrote: I need help setting up an array to read data into. I would like to read data from 3 columns on one sheet into an array and then copy that information into another sheet. The Array/Matrix will be 3 columns wide, but the number of rows will need to be dynamic. Can anyone help? TIA E |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Matrix Math using LOOKUP inside Array {} Function | Excel Worksheet Functions | |||
Matrix/array question | Excel Discussion (Misc queries) | |||
mirrored array/matrix | Excel Discussion (Misc queries) | |||
How to use an array or matrix to return text vs. numeric values | Excel Worksheet Functions | |||
Lookup of sorts...Matrix Array etc. | Excel Programming |