Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Assembling large "array"
I am using Excel 2003 on Windows Vista
I have a file of London fare data. (Shows fares between origin and destination stations.) It consists of about 600 rows and columns. Because Excel 2003 only supports 256 columns in a sheet, I need to split my data over more than one sheet. The data ought to be symmetrical and mostly is. However, I have found one pair of stations where the AB price and BA price are different. It takes several elapsed days to assemble that file from the Internet. I would like to identify asymmetric data to assess the extent of that asymmetry. i.e. I assume AB price = BA price is probably correct. I want to assemble an "array" in memory. I can read data from a single sheet with code like this: Dim PricesA_D As Variant PricesA_D = Sheets("A-D").Range("B2", _ Sheets("A-D").Cells.SpecialCells(xlCellTypeLastCell).Address ) ' The first row and column contain station names I think I need a picture. Sheets("A-D") might start Abbey Wood Acton Central Acton Main Line ... Abbey Wood X 1.23 2.34 Acton Central 3.21 X 3.45 Acton Main Line 2.34 3.45 X .... I have shown asymmetric data between Abbey Wood and Acton Central. Sheets("E-K") station names might start Ealing Broadway Ealing Common Earls Court ... Abbey Wood Acton Central Acton Main Line .... I want an "array" in which the E-K data is appended to the A-D data. I tried Dim Prices As Variant Prices = Application.Union( _ Sheets("A-D").Range( "B2", _ Sheets("A-D").Cells.SpecialCells(xlCellTypeLastCell).Address ), _ Sheets("E-K").Range( "B2", Sheets("E-K").Cells.SpecialCells(xlCellTypeLastCell).Address ), _ Sheets("L-R").Range( "B2", Sheets("L-R").Cells.SpecialCells(xlCellTypeLastCell).Address ), _ Sheets("S-Z").Range( "B2", Sheets("S-Z").Cells.SpecialCells(xlCellTypeLastCell).Address )) I got Run-time error '1004': Method 'Union' of object '_Application' failed I assumed that was because Union tries to create a sheet with more than 256 columns. To test that assumption, I tried Prices = Application.Union( _ Sheets("A-D").Range("B2", "T20"), _ Sheets("E-K").Range("B2", "T20")) but got the same error. 1) Why does Union get 1004? 2) How do I construct a large "array" with minimal coding? 3) After Dim PricesA_D As Variant PricesA_D = Sheets("A-D").Range( _ "B2", _ Sheets("A-D").Cells.SpecialCells(xlCellTypeLastCell).Address ) Locals shows StationsA_D has Type Variant/Variant(1 to 623, 1 to 151) What code will find those values 623 and 151 in StationsA_D? My original problem of finding asymmetric data can easily be solved by comparing cells on my 4 sheets. I want to be able to process copies of sheet data in memory. -- Walter Briscoe |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Assembling large "array"
1. A Range is a class *within* another class, Worksheet. You can use
Union to combine two Ranges, both from the same Worksheet, but you can't make one Range from two different Worksheets. 2. Since Prices is a Range, you should have used Set Prices = ... 3. I think you will have to settle for creating an array that is not a Range, e.g. as follows. Dim dArr(1 To 3, 1 To 6) As Double Dim iRow As Integer Dim iCol As Integer For iRow = 1 To 3 For iCol = 1 To 3 dArr(iRow, iCol) = Sheets("A-D").Cells(1 + iRow, 1 + iCol).Value Next iCol For iCol = 4 To 6 dArr(iRow, iCol) = Sheets("E-K").Cells(1 + iRow, iCol - 2).Value Next iCol Next iRow For this I changed your X's to 0's. If you want to use X's, declare dArr as Variant. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Assembling large "array"
In message
..com of Thu, 23 Feb 2012 07:05:06 in microsoft.public.excel.programming , merjet writes 1. A Range is a class *within* another class, Worksheet. You can use Union to combine two Ranges, both from the same Worksheet, but you can't make one Range from two different Worksheets. Thank you for that perception. It NOW makes sense to me. The output of Union is a Range. Therefore that must fit the criteria for a Range. i.e. It must only refer to one sheet/ 2. Since Prices is a Range, you should have used Set Prices = ... You're right. I find Set quite unnatural, and regularly fall over my failure to use it. I do not understand why Prices = Range("B2", "D4") does not give a compilation error. Instead it gives a "Run-time error '91': Object variable or With block variable not set". Dim foo2 As Long: foo2 = "Hello, World!" also does not give a compilation error. I guess VBA authors decided to economise on compilation checks. The logic is that you don't check because you can't always check. I feel better for composing those thoughts. ;) 3. I think you will have to settle for creating an array that is not a Range, e.g. as follows. Dim dArr(1 To 3, 1 To 6) As Double Dim iRow As Integer Dim iCol As Integer For iRow = 1 To 3 For iCol = 1 To 3 dArr(iRow, iCol) = Sheets("A-D").Cells(1 + iRow, 1 + iCol).Value Next iCol For iCol = 4 To 6 dArr(iRow, iCol) = Sheets("E-K").Cells(1 + iRow, iCol - 2).Value Next iCol Next iRow For this I changed your X's to 0's. If you want to use X's, declare dArr as Variant. The problem with that useful example is that it makes many accesses to the sheet. I was going to ask how I could find the dimensions of a variant array after filling it. [Why is Set not needed in the code below?] Dim PricesA_D As Variant PricesA_D = Sheets("A-D").Range( "B2", Sheets("A-D").Cells.SpecialCells(xlCellTypeLastCell).Address ) After running that code, In the Immediate Window, I showed: ?ubound(PricesA_D) 623 ?ubound(PricesA_D,1) 623 ?lbound(PricesA_D) 1 ?ubound(PricesA_D,2) 151 ?lbound(PricesA_D,2) 1 and in the Locals Window, I showed PricesA_D Variant/Variant(1 to 623, 1 to 151) and PricesA_D(1) Variant(1 to 151) .... PricesA_D(623) Variant(1 to 151) I now have enough information to code my problem as I wish to do. Thank you very much to merjet for help. -- Walter Briscoe |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Assembling large "array"
Just to add some food for thought...
The array could have been loaded like this... PricesA_D = Sheets("A-D").UsedRange ... and its dims could have been retrieved like this... Debug.Print UBound(PricesA_D) & vbTab & UBound(PricesA_D, 2) -OR- With Sheets("A_D").UsedRange Debug.Print .Rows.Count & vbTab & .Columns.Count End With To access the data in code you can do something like this... Dim i&, j& 'as Long For i = LBound(PricesA_D) To UBound(PricesA_D) For j = LBound(PricesA_D, 2) To UBound(PricesA_D, 2) 'process each element in Dim2 for each element in Dim1 PricesA_D(i, j) = PricesA_D(i, j) Next 'j Next 'i -- Garry Free usenet access at http://www.eternal-september.org ClassicVB Users Regroup! comp.lang.basic.visual.misc microsoft.public.vb.general.discussion |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Help using the LARGE function "WITHOUT" using an array | Excel Worksheet Functions | |||
Using the functions "LARGE"and "SMALL" in an Excel program | Excel Programming | |||
Listbox header inside VBA (Array("Head1", "Head2", ...) | Excel Programming | |||
If changed array formula reduce ""\""\""\ - signs to #Missing, will it make ... | Excel Programming | |||
Utility to "clean up" or "defrag" large Excel file | Excel Discussion (Misc queries) |