YTLE#63.5: Range of Array Formulas

Hello all,

I am trying to delete a range which has historical stock data from an array
formula. I want to reformat the cells in the range and then I will reinsert
the formula. No matter what I do i get this dialog box;"You cannot change
part of an array". Are those cells stuck there for good? How does one delete
that range?

TY

Hi,
I am trying to transfer the contents of an array into a range of Excel (red line in the code below). Though I am able to do that when the array has values, however, it is giving me an error when the array has formulas.
How can I get this to work?

Code:

          Dim Vma As Variant
 ...
          TotalRows = .Range("Spread").Count

          Vma = Application.Transpose(.Range("Spread"))
           
          
           For i = TotalRows To 1 Step -1   

           Vma(i) = "=RC[-2]- RC[-1])"

           Next
            
            .Range("Spread") = Application.WorksheetFunction.Transpose(Vma)  
 ...

Thanks

MG.

Hello~ I'm hoping that someone can help me with my slow array formula plus eliminate the need to adjust my print range each day so blank lines won't print. I have a month-to-date report with a 'print' sheet which the # rows varies daily between 10 rows (Day 1) and approx. 250 rows (by day 30) depending on which non-contiguous (up to 717) rows are used in the 'data' sheet. The data sheet summarizes other sheets in the monthly file. I use an array formula (found online - no array expertise here!) to summarize the 'data' sheet of 717 rows as such, so my 'print' sheet doesn't have blank lines in between lines with data. Here's my formula on the print sheet, copied down in col. A to cover all 717 rows calculated in the data sheet:

{=IF(ISNA(IF(AND(ISERROR(MATCH(0,COUNTIF(A$6:$A6,MAY2010!$A$7:$A$717&""),0)),MAY2010!E$70),"",INDEX(IF(ISBLANK(MAY2010!$A$7:A$717),"",MAY2010!$A$7:$A$717),MATCH(0,COUNTIF(A$6:$A6,MAY2010!$A$7:$A$717&""),0)))),"",IF(AND(ISERROR(MATCH(0,COUNTIF(A$6:$A6,MAY2010!$A$7:$A$717&""),0)),MAY2010!E$70),"",INDEX(IF(ISBLANK(MAY2010!$A$7:A$717),"",MAY2010!$A$7:$A$717),MATCH(0,COUNTIF(A$6:$A6,MAY2010!$A$7:$A$717&""),0))))}

My print range TODAY is "$A$1:$H125,$R$1:$X$15", but each day I have to adjust the A1:Hxxx portion, depending on how many lines my array formula has summarized from the data sheet. The spreadsheet can take 2 mins. to calculate the print sheet between entries to the data sheet. I'm concerned that my array formula may be inefficient (although it's effective), plus I'd love to know how I could avoid having to adjust my print range every day. Does anyone out there know how I could improve these processes? Many thanks for any help you can give me.

I have ranges made up of non-adjacent cells from one worksheet and I
get a #VALUE! error when I try to do any conditional count or sum
calculation with an array formula on either or both of them. Each
range is a selection of 32 non-adjacent cells from a single column.
The cells contain array formulas that return percentages.

Example of range values
I8: 5.9%
I11: 12.1%
I14: 22.3%
I17: 0.0%

I was able to get values returned from simple functions like Max and
Min but the following example gives me the #VALUE! error:

{=SUM(IF((Rng>0),1,0))}
=COUNTIF(Rng,"<.0125")

Any wisdom would be appreciated!! Thanks

Hi all,

I have a table with 250 rows and columns A-AD. Each row has different types of information about a "bank paper". The table must be unsorted due to several reasons. I have to summarize/count the papers type by type. For example coloumn AD has the information if the paper's type is "NEW" or "DELETED". Also AC has the information if it is paper "Type 1" or paper "Type 2" etc.

I have to do many counts like this: count how many NEW, Type 1 paper exist in the table. I do this with array formulas since there are many aspects I should "filter", but it is too slow.

My question is: is it possible to collect the correct rows, for example all "NEW" rows into an array somehow? I want this to fasten up the search, since now the array formulas for finding NEW & Type 1 and NEW & Type 2 searches two times for NEW, what would not be necessary if I could collect all NEW rows somehow into someting like an "array-cell".

(I tried to do it with OFFSET, MATCH, INDIRECT and Ctrl-Shift-Enter with no luck, also thought about INDEX but I think these formulas are not for this.)

I have Excel 2003 (and two days ).

I am looking to dynamically fill the contents of the array below. Instead of manually inputting the names of workbooks I want the contents of the array to auto populate with the contents of cells Range(a12:a27). The other complicating factor is the contents of those cells are only part of the filename, ".xls" must be added to the end of the contents of each cell in the range.

I am trying to clear a number of workbooks from one workbook without opening the others which already works I would just like the vbs to reference the cells that can be easily changed within the worksheet so when I leave my current position no one has to know how to look in the vbs to change the filenames to add/subtract/change file names of the workbooks to clear.

VB:

 
Sub ClearBooks() 
    Dim ex As Excel.Application 
    Dim wrkbk As Workbook 
    Dim sht As Worksheet 
    Dim books As Variant 
    Dim folder As String 
    Dim i As Integer 
     
    folder = "FILEPATH" 
    books = Array("WORKBOOK1.xls", "WORKBOOK2.xls", "WORKBOOK3.xls", "WORKBOOK4.xls") 
     ' a12, a13, a14, a15, a16, a17, a18, a19, a20, a21, a22, a23, a24, a25, a26, a27
    Application.ScreenUpdating = False 
    Application.DisplayAlerts = False 
     
On Error Goto errH: 
    Set ex = New Excel.Application 
    For i = 0 To UBound(books) 
        Set wrkbk = ex.Workbooks.Open(folder & books(i)) 
        Set sht = wrkbk.Sheets(1) 
        sht.Range("A2:H21").ClearContents 
        sht.Range("K4:P5").ClearContents 
        wrkbk.Close True 
    Next 
    ex.Quit 
    Application.ScreenUpdating = True 
    Application.DisplayAlerts = True 
    Exit Sub 
errH: 
    ex.Quit 
    Application.ScreenUpdating = True 
    Application.DisplayAlerts = True 
    MsgBox Err.Description 
End Sub 

If you like these VB formatting tags please consider sponsoring me in support of injured Royal Marines

Dear All,

I'm trying to write some code to do a fancy series of searches, but before I can do that, I need to create a list of unique elements (ie get rid of duplicates). I used an example I found somewhere to write my own function that does the job, and works fine for arrays defined item by item in excel vba.

Code:

Function funUniqueList(vaArrayIn() As Variant) As Variant

Dim vaUniqueList() As Variant 'Stores unique values
Dim strElement As String 'Stores the element that is being investigated
Dim bIsUnique As Boolean 'Stores whether the current item is still unique after comparing to each other element

ReDim vaUniqueList(0 To 0) As Variant 'Resize to allow for storage

vaUniqueList(0) = vaArrayIn(0) 'First item must be unique

For i = 0 To UBound(vaArrayIn)
    strElement = vaArrayIn(i) 'Stores each element one at a time
    
    For j = 0 To UBound(vaUniqueList) 'Checks the element against the growing list of unique elements.
        If strElement = vaUniqueList(j) Then 'If it matches any, the element is not unique and the subloop is exited
            bIsUnique = 0
            Exit For
        End If
    Next j
    
    If bIsUnique = True Then 'If the element is unique, it is added to the list of unique items
    ReDim Preserve vaUniqueList(0 To UBound(vaUniqueList) + 1)
    vaUniqueList(UBound(vaUniqueList)) = strElement
    End If
    
    bIsUnique = 1 'The value is reset
Next i

funUniqueList = vaUniqueList 'Creates output for function

End Function

This works fine on arrays that are defined via excel vba enumerating each element. However, if I try to pass an array I created by reading in from a range of values it gives me a "subscript out of range" error. If I try to redim the range it also gets angry, although for different reasons. What is the right way to do this? My attempt is below.

Code:

Sub ArrayPassTest()

Dim vaRawList() As Variant 'To Be Read in from Excel spreadsheet
Dim vaUniqueValues() As Variant 'To store result from uniquelist function

vaRawList = Range("A2:A317").Value 'Read in value from range

vaUniqueValues = funUniqueList(vaRawList) 'Run the function and store the resulting array

Range("B1").Resize(UBound(vaUniqueValues), 1).Value = vaUniqueValues 'Write the values to the spreadsheet

End Sub

The data of interest is just a bunch of numbers (that I treat as strings really) that are all 9 digits long. Thanks much for your help.

This is the example I found:
Code:

Assume: Column A consist a list of data with blank row (in this example: A5, A7, A10, A11)
A2: Planning 
A3: Your
A4: Career
A5: 
A6: Change
A7: 
A8: Identify
A9: The goals
A10: 
A11: 
A12: Pay attention
A13: to what you see
Go to Insert menu | Name | Define
In the Define Name dialog box, enter the following in the 'Names in workbook' box BlanksRange
Then enter the following formula in the "Refers to" box =Sheet1!$A$2:$A$10
Click Add
In the Define Name dialog box, enter the following in the 'Names in workbook' box NoBlanks
Then enter the following formula in the "Refers to" box =Sheet1!$B$2:$B$10
Click Add, then OK
Type the following formula in cell B2:

=IF(ROW()-ROW( NoBlanksName)+1>ROWS( Name)-COUNTBLANK( Name),"",INDIRECT( ADDRESS(SMALL((IF( Name"",ROW( Name),ROW()+ROWS( Name))),ROW()-ROW( NoBlanksName)+1),COLUMN( Name),4)))

This is an array formula, so we must press Ctrl+Shift+Enter rather than just Enter when first enter the formula, and when ever we edit it later. Excel will automatically add the braces. 
If we enter or array formula without pressing Ctrl+Shift+Enter, it will return an incorrect result or a #NUM! error. -- which is easily overcome by pressing F2 as if to edit the cell's contents, then Control-Shift-Enter to re-enter the formula correctly. It will display:

{=IF(ROW()-ROW( NoBlanksName)+1>ROWS( Name)-COUNTBLANK( Name),"",INDIRECT( ADDRESS(SMALL((IF( Name"",ROW( Name),ROW()+ROWS( Name))),ROW()-ROW( NoBlanksName)+1),COLUMN( Name),4)))}

Then copy this formula to cell B3: B13, this will result:
B2: Planning 
B3: Your
B4: Career
B5: Change
B6: Identify
B7: The goals
B8: Pay attention
B9: to what you see
No blanks are copied to column B. Hope this formula what are you looking for.

But when I put it on page 4 and test it, it doesn't work. For all I know it may never have worked because it was on yahoo answers. Can you help me make one that works for the same thing?

I have a table with an if formula that brings a list out to the right of the original list, I need this formula to take that list to another page and take all the spaces out of it. Is it possible to do this without vba?