Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
adding columns to an arrray
How to I load into an array values from a worksheet that are not in a
contiguous range? For example a 3 column array made up of (from the same row): value in F, sum of D - G, value in S -- WinXP - Office2003 (Italian) |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
adding columns to an arrray
You could loop through the rows and build your array.
You could pick up each non-contiguous column and build a multi-column array in your code. You could pick up the entire range at once and then just ignore the columns you don't need. Or you could even pick up each non-contiguous column and use 3 different arrays, but loop through them in sync. Here's an example of the first: Option Explicit Sub testme() Dim myArr() As Variant Dim HowMany As Long Dim myRng As Range Dim myCell As Range Dim wks As Worksheet Dim rCtr As Long Set wks = Worksheets("Sheet1") With wks 'building the array based on column F Set myRng = .Range("F1", .Cells(.Rows.Count, "F").End(xlUp)) End With HowMany = myRng.Cells.Count 'how many rows by 3 columns ReDim myArr(1 To HowMany, 1 To 3) rCtr = 0 For Each myCell In myRng.Cells rCtr = rCtr + 1 myArr(rCtr, 1) = myCell.Value myArr(rCtr, 2) = Application.Sum(myCell.EntireRow.Range("D1:g1")) myArr(rCtr, 3) = myCell.EntireRow.Range("s1") Next myCell 'just to show it works For rCtr = LBound(myArr, 1) To UBound(myArr, 1) MsgBox myArr(rCtr, 1) & vbLf & myArr(rCtr, 2) & vbLf & myArr(rCtr, 3) Next rCtr End Sub I used Application.Sum(Dx:Gx) to determine the sum. That includes column F. So if F contains a number, it'll be included in the sum. David Macdonald wrote: How to I load into an array values from a worksheet that are not in a contiguous range? For example a 3 column array made up of (from the same row): value in F, sum of D - G, value in S -- WinXP - Office2003 (Italian) -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Adding Columns, Then deleting old columns | Excel Discussion (Misc queries) | |||
looking up multiple values in an arrray | Excel Worksheet Functions | |||
{SUM(IF((ARRRAY FORMULA)} | Excel Worksheet Functions | |||
Set a 2D arrray data into a range, given the top-left cell | Excel Discussion (Misc queries) | |||
help with arrray formula | Excel Programming |