One Dimension Array From 1 Column Range

Oct 9, 2007

I am trying to write a function that grabs the value of some cells in a column and returns a String array with those values. I am getting a compiler error on the line where I assign a value to an array element. It appears to be assuming that rather than an array reference, I am trying to do a recursive call to the function. The error is "Function call on left-hand side of the assignment must return Object or Variant." I would have done this with by assigning the range directly to the array but the data is in a column rather than a row; don't know of a more elegant way to do it.

Public Function projectList() As String()
Dim c As Range
Dim i As Long
For Each c In Range("FirstProject"). CurrentRegion
Redim Preserve projectList(UBound(projectList) + 1)
i = UBound(projectList)
projectList(i) = c.Value ' *** Compiler error occurs here ***
Next c
End Function

View 3 Replies


ADVERTISEMENT

Cycling Through 1 Dimension Of An Array

Aug 17, 2009

Using index, I know passing 0 as the row or column num returns the entire row or column.

So I have an array containing worksheets, and an associated column number:

View 3 Replies View Related

Is Two Dimension Array Empty

Feb 21, 2007

I'm trying to write a function that I can pass an array along with the data I want added to that array. The function will resize the array, put the data into it and return the new array. Example:

Function AddNewDataToArray(MyArray As Variant, Arg1 As String, Arg2 As String, Arg3 As String) As Variant
'If no elements have been added
Redim Preserve MyArray(1 To 3, 1 To 1)
'Add the first record of data to the array.

'If there is already data in the array
Redim Preserve MyArray(1 To 3, 1 To UBound(MyArray, 2))
'Add the next record of data to the array
End Function

As 2 dimensional arrays are Base 1, the challenge I have is getting it to resize (redim preserve) the array when it has to add the first record. I thought I could just use an IsArray function to test if there had been any data added. If not, then it would run the following...........

View 3 Replies View Related

Redim - Re-dimension An Array

Nov 16, 2007

Currently I am building a class to keep track of entries I have made during the macro execution. Thus far I have:

Private Type Memory
MemoryArray() As Variant
End Type

Private Sub Class_Initialize()
Redim MemoryArray(0) As Variant
End Sub
Public Property Let AddToMemory(Object As Variant)
'memory is empty
If UBound(MemoryArray) = 0 And MemoryArray(UBound(MemoryArray)) = "" Then
MemoryArray(0) = Object ..............

View 5 Replies View Related

Passing An Entire Dimension Of An Array To A Function ..

Jul 9, 2009

Passing an entire dimension of an array to a function....

View 13 Replies View Related

Program Multiple Elements Of Multi Dimension Array Simultaneously

Aug 23, 2012

Is there any way to do this part of code without calling out each element individually?:

VB:
Dim aSequence(7, 1) As Integer
'setup Evaluation sequence
aSequence(0, 0) = -1

[Code]......

View 2 Replies View Related

Whole Column Range In A Sumproduct Array

Jul 29, 2009

I've created a spreadsheet with SUMPRODUCT formulae, which is working fine for now.

However, these formulae include arrays with ranges of, for example, $G$2:$G$600. What we need to do is, instead, reference the while column as far down as it goes, forever as the range of the array. This applies to multiple occurrences.

Every formula I have found for this may work on of itself, but does not work with the SUMPRODUCT formulae I have used.

For reference, an example:

=SUMPRODUCT(--('BG NEW DB'!$D$2:$D$600=$B$2),--('BG NEW DB'!$G$2:$G$600=C$2),--('BG NEW DB'!$H$2:$H$600=$A$2),--('BG NEW DB'!$AD$2:$AD$600="Y"))

View 10 Replies View Related

How To Make Box With Dimension

Oct 26, 2011

I just need to know that is it possible in excel that we could make box with length, width, height dimensions?

For example: we just enter 100mm is length, 70mm is width and 30mm is height.. so excel will make box according to this size

View 3 Replies View Related

Print Area Dimension

Feb 26, 2010

I used the macro recorder for this:

ActiveSheet.PageSetup.PrintArea = "$A$1:$P$246"

How would I change this to use variables?

I need to set the print area to A( FR ) to P( LR )

Of course FR is first row, LR is last row

View 9 Replies View Related

Copy Range Into Array Then Array Back Into Range

Jul 25, 2012

I want to know how to copy a range into an array then an array back into an range.

Code:
Dim a(3,3) As Double
a(3,3) = Range("C3:E5")
Range("C10:E12") = a(3,3)

View 1 Replies View Related

The Specified Dimension Is Not Valid For The Current Chart Type

Feb 19, 2010

when opening the workbook with Excel 2010 beta I folllowing error:

Run-tim error '1004':

The specified dimension is not valid for the current chart type.

View 9 Replies View Related

Multiple Dimension Crosstab Convert To Flat File

Oct 6, 2012

VB:
Sub CreateFlat()
Dim wsData As Worksheet
Dim wsNew As Worksheet

[Code].....

In 2006 posting, this code was presented to the Forum. It works perfectly for a one dimension Crosstab. I have 4 dimensions that I need copied to the output.

I have attempted to modify the code, but nothing appears to adjust the pull of more than the first column of dimension data.

see the attached spreadsheet for the Crosstab data and desired output.

View 8 Replies View Related

Looking Up Row Array Data In Column Array

Jul 11, 2007

On one sheet (KPI) I have either the values "x" or "" in the range A84:A89 to mark wheter to use the corresponding project in the range B84:B89. On the sheet X-ref I have the same project names in range T4:Y4 and a corresponding target value in T8:Y8

What I want is the sum (or average) of the marked-projects target-values. The result should end up in KPI!G31.
In other words I want

=sumif(A84:A89;"x";'X-ref'!T4:Y4)

but it doesn't work since the first range is an column-array and the second range is an row-array.

View 5 Replies View Related

Nested Index / Match Array - Return Value In Column C When Matching Column A And B But With Few More Criteria

Jun 7, 2014

I am trying to use a nested INDEX and MATCH array formula to return the value in column C when matching column A and column B, but with a few more criteria.

The range containing all the data

A
B
C

1
Cat 1
January 1, 2014
John

[Code] ..........

I am looking for the array formula to return the name of the person in column C who is in Cat 1 after the date in column B.

For example;
C7 should return "John" because B7 requests "January 15, 2014", which is after the value in B1
C8 should return "John" because B8 requests "February 15, 2014", which is after the value in B1
C9 should return "Andrew" because B9 requests "August 15, 2014", which is after the value in B4

The best try I had for the formula in C7 was

{(INDEX($A$1:$C$4,MATCH(1,($B$1:$B$4>=B7)*(A$1:$A$4=A7),0),3))}

This brings back "John" as desired in C7, but when copying down the table into C8 and C9 both C8 and C9 return Andrew.

I guess this is due to my ">=" condition in the Match formula and it is returning "Andrew" because "Andrew" is also after the date requested, but I cannot for the life of me work out how to get it to work.

View 2 Replies View Related

Display Column Header If Any Value Other Than 0 Exists In Multi-Column Array

Jul 22, 2014

I would like to display the column header of the row in which a value other than 0 exists in a cell for each instance that value exists in an array spanning 3 columns. So the result cell could be any of the three column headers.

I started the formula in P2 of the GL Detail-2012 tab. (FILE TOO BIG)

Excerpt:

Resin Sand Eliminated Segment
- (12,896.65)-
- (6,570.85) -
- (11,503.80) -
- (13,188.50) -

If there is any value other than 0 in Resin, Sand or Eliminated columns, return that column's header under Segment.

Here is what I started: =INDEX($M$1:$O$1,SUMPRODUCT(COUNTIF($M$2:$O$67756,)))

View 2 Replies View Related

Search For Value In Multiple Column And Row Array And Return Value In First Column?

Dec 16, 2013

I have an array 20 Rows x 42 Columns, which contains a competition draw.

I need to search this array for a unique value and return whatever the time is in the first column on the same row as the value appears, and enter it into column C in the Womens Times sheet.

I also want to return into column D the court number from row 3.

The reason i want this automated is as teams enter / withdraw we may need to drag the games from court to court to fill gaps, so i want the Womens Times sheet to update accordingly.

I have been messing around with index and match, but cannot quite get it to return what i need.

I have attached an example ... on the sheet "Womens Times" in column A there is a list of game numbers ( #1W etc etc) indicating womens game #1 and so on. The main sheet i am using also has a seperate tab for the mens games, hence the designator of W or M on the end of the game number.

View 2 Replies View Related

Range/Array Use

Oct 31, 2008

I have data in a spreadsheet that I would like to send to another sheet as follows:

Use the values in the range $D$2:$D$12 and $F$2:$F$12 to reference the column and row of a cell and the values in the range $G$2:$G$12 to reference a specific group of cells on another sheet into which would be inserted the value found in the range $C$2:$C$12. The best I have come up with is to place a formula in each of the cells in the second sheet that would state:

IF a value in the range $D$2:$D$12 matches the column # of the current cell AND IF the corresponding value in the range $F$2:$F$12 matches the row # of the current cell AND IF the corresponding value in the range $G$2:$G$12 matches the region of the current cell THEN the value of the current cell EQUALS the value of the corresponding cell in the range $C$2:$C$12.

I think this will work but I don’t have enough knowledge of Excel to write this formula.

View 9 Replies View Related

Array To Range

Jun 9, 2009

This code works!

Dim rTmp As Range
Dim aTmp As Variant

Set rTmp = Range("A1:D21")
aTmp = rTmp.Value
Range("A1:D21") = aTmp
If you exchange Range("A1:D21") with rTmp.value it does not, why?

This code doesn't work?

Dim rTmp As Range
Dim aTmp As Variant

Set rTmp = Range("A1:D21")
aTmp = rTmp.Value
rTmp.Value = aTmp

View 9 Replies View Related

Sumifs With Array Sum Range?

Feb 7, 2014

Attached file should be self-explanatory.

I want to be able to set sum range using lookup or match.. For example if I change B2 value to Nov-13, I want it to sum column K.

View 5 Replies View Related

How To Get Array Of Addresses From Range

Jun 19, 2014

I have a table that looks like this:

| A | B | C | D | ...
1 | fu | bar | lab |...
2 | rab| uf | luv |...
3 |...

All of my values are within the Range "A1:C2"

I would like to have a variant array which contains the addresses of this range.

Things I tried that didn't work:

If my variant is V and my range is R,

v = r returns an array which contains
fu|bar|lab
rab|uf |luv

V = R.Address gives me
A1:C2|A1:C2|A1:C2
A1:C2|A1:C2|A1:C2

What I actually want:
A1|B1|C1
A2|B2|C2

i know this can easily be done with a loop, but the table I want to use this on is huge, and a loop takes hours to execute.

View 1 Replies View Related

Array To Set Range Objects

Dec 29, 2008

Hi all, starting this as a new problem because it's so far different from what I was originally talking about; but this does relate in part to my previous thread.

Anyway. I'm trying to set an array to set Range objects so that I can define each one as a seperate With block. Here's what I "know" when starting out.

I developed the following. I keep thinking it should be multidimensional but my sleep deprived brain came up with this instead. The problem is, it's throwing a 1004 Method "Range of object '_Worksheet'" failed at the With statement.

View 6 Replies View Related

Range Array Sort

Oct 20, 2009

I am trying sort a bunch of different ranges. So I am trying to use the same sort code but run an array of ranges through. This code is for only two ranges
CA3:CD200 and CF3:CI200. I want to pass the ranges as variables through the sort code but I cannot get it to work.

View 4 Replies View Related

Change And Range To An Array

Jan 5, 2010

Through VBA is it possible to give a range of cells that should be converted to array formulas? As part of my macro I am copying a sheet from one book to another. This sheet contains a lot of formulas and so as not to keep the reference of the old book I find and replace "=" with "#" and then swap them back after the move. This works fine for all normal formulas but not the Array formulas.

If someone could tell me how to give and range and convert them all to arrays or can think of a better method of moving a sheet from one book to another without the formulas keeping the references to old book.

View 5 Replies View Related

How To Recognize Range As Array

Sep 3, 2012

I have created several functions in VBA that operate on arrays created by other functions in VBA, however they don't recognize cell ranges as arrays. For example, the UBound function doesn't work with a range argument within my function.

How can I get my functions to recognize cell ranges as arrays, as well as arrays that I have created directly in VBA?

View 9 Replies View Related

Setting Range For Array

Oct 16, 2012

I am trying to set an Array to prompt user with a series of questions and input answers into a specific row. However, I want to skip over certain columns and leave those blank. How would I modify my code to do this?

Code:

Private Sub CommandButton2_Click()
Dim ArrQues() As Variant
Dim ArrInput(2) As Variant
Dim lngLstRow As Long
Dim strPNfromSales As String
ArrQues = Array("Enter the Part Number.", _ "Quantity Being Returned?", _ "What is the Reason for Rejection?")
For b = LBound(ArrQues) To UBound

[Code]...

I want the first two questions to be input into Column B & C, but then the third question to be input into column I.

View 2 Replies View Related

Filling Range With Array

Oct 9, 2013

I have a quick question in relation to filling a range with an array.

When I use the following code:

Code:
Sub justdoit()
s = Array(1, 2, 3)
Range("A1:C1") = s
End Sub

It works fine. but when I try to populate the range in rows instead of colums, the array value stays as the first value.

Code:
Sub justdoit()
s = Array(1, 2, 3)
Range("A1:A3") = s
End Sub

I'm experimenting with arrays and would like to get better at them, but this has me stumped for the moment. How to get the values to work going down, like they do going across.

The above code is a small sample of data, I am wating to replicate with using over 80K lines and cell by cell is too slow.

View 2 Replies View Related

Paste Array Into Range

Jun 4, 2014

I have below code:

Code:
myRange = Range(Cells(5, 18), Cells(dRow, 30))
For Each cell In myRange
If IsError(cell) Then
cell = ""
Else
cell = Round(cell, 3)
End If
Next cell
Range(Cells(5, 18), Cells(dRow, 30))= myRange

The problem is that the range does not change after I paste modified array back to it.

View 9 Replies View Related

Typecast Array Into Range

Mar 15, 2004

Is type-casting possible in Excel ? For instance, can i typecast an array into a range, and vice-versa ?

View 6 Replies View Related

Read A Range Into An Array

Aug 10, 2007

In a Worksheet named "Input" and in Cells "B3:G?" I have 6 number groups. How can I ...

( 1 ) Put each of the 6 number groups into an array when I don't know what the last row number is.

( 2 ) Find the maximum value in any of those cells and attach it to a variable named MaxVal.

... so once I have done this I can then loop through each 6 number group and perform some calculations.

View 9 Replies View Related

Array And Range Reference

Jan 9, 2009

In a worksheet is a table:
a b c d
e f g h
i j h l
m n o p
q r s t

I'm looking for a procedure to (with the help of
an array) reproduce this table exactly under the original one
with one row between them but in the folowing form:

q m i e a
r n j f b
s o h g c
t p l h d

the procedure should be applicable on any haphazard
regardless of its place on a worksheet

View 9 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved