Excel Array Formula Series #3: Expected Return For Stocks

Hi all! For the formula below, I copied the colored sections and pasted them into separate cells. Each section returns the expected value when tested alone in a separate cell. But when I put them together in the whole, I get something unexpected. My best guess is that I have a ) in the wrong position. The solution must be painfully obvious, but I don't see it.

Because it's long, I've color coded it for convenience.

=IF (LOOKUP(jos_vm_order_item!L329,{"C","P","S","X"},{"Paid","Unpaid","Shipped","Cancelled"})="Cancelled","Cancelled",IF(LOOKUP(jos_vm_order_item!L329,{"C","P","S","X"},{"Paid","Unpaid","Shipped","Cancelled"})="Unpaid","Unpaid", IF (VLOOKUP(VLOOKUP($A253,jos_vm_order_item!$1:$1048576,5,FALSE),jos_vm_product!$1:$1048576,32,FALSE)

I have a function in which I return an array--however, when i go to set the completed array from within the function ("original" in my example) i receive the "Compile Error: Expected Array"

The array IssueMaturities is not declared anywhere else other than the function statement.
The array "original" is not declared anywhere else other than the function statement.

Here is the code that results in the error.
Code:

Function IssueMaturities(original() As String) As String
    Dim index As Integer
    Dim tempValue As String
    index = 0
    Do
        original(index) = Format(original(index), "YYYY")
        tempValue = original(index) 'no messing with original
        If tempValue < Format(Now, "yyyy") Then 'if year has passed, then...
            tempValue = original(index + 1) ' set this value to the following
        End If
            original(index) = tempValue 'replace index value with next value
            index = index + 1 'increment index
    Loop Until index = 31
    Do
        tempValue = Format(original(index), "YYYY") ' no messing with original
        If tempValue = original(index - 1) Then ' if current value is equal to next
            ReDim Preserve original(0 To index - 1) 'then shrink array by 1
        End If
    Loop Until index >= UBound(original) ' continue until the (new) end has been reached
    ReDim IssueMaturities(LBound(original) To UBound(original))
    IssueMaturities() = original()
End Function

the ERROR occurs on the ReDim statement near the end of the function. I am attempting to resize the IssueMaturity array to reflect the size of the "original" array.

I'm really confused as to what I'm missing...
Thanks in advance!

here is my situation.

I have a large grid of part numbers (col A, col C , col E .....) with there asssociated prices (col B, col D, col F ....).

in excel if created an array col AA = col B
col AB = col D
col AC = col F
col AD = col H

using the following = if(small(AA2:AD2,1)>0,small(AA2:AD2,1),if(small(AA2:AD2,2)>0,small(AA2:AD2,2),if(small(AA2:AD2,3)>0,small(AA2:AD2,3),small(AA2:AD2,4))))

this give you the smallest NON-ZERO value in the array......
how can this be done in ACCESS............. (I wrote an 8 level if than instruction that is 500 characters long......

is there anyway to load info into and array and test it in a similar fashion...or if you can think of a better way....that would be great.

Hello;

1) I need to calculate M43:: sum[m=1 to m=9] G(m)*sin(m*x) for each value of tabulated x in column L.
The 9 values of "m" are tabulated in $D$21:$D$29
The 9 values of G(m) are tabulated in $C$21:$C$29
The first x value is in cell L43

Can someone please help in deriving the array formula to be entered in M43 ??
For other values of x in L44, L45, ... one would simply copy M43 and paste in M44, M45, ...

2) If it's not too much trouble, how about:
N43:: -sum[m=1 to m=9] G(m)*cos(m*x)/m for each value of tabulated x

Your help would be greatly appreciated.

Regards.

How to create a number series within an array formula, I mean, the series starts with 1 and will finish with 8 for example;
{1;2;3;4;5;6;7;8} - It will be the result within my array formula.
In Aij matrix means i=1 to 8 {i+1;i+1;i+1;i+1;i+1;i+1;i+1;i+1}.
I need to retrieve it within an array formula.

How can I do that just using an array formula.



Luthius

I've got an array...
Code:

Const NPANXX_OUT As Integer = 5
Const RATECR_OUT As Integer = 6
Const COMNTY_OUT As Integer = 7
 
Dim LastRow(7) As Long

It's used to hold the last row value for a certain set of columns.
Code:

Sub GetLastRows()
    Dim i As Integer
    For i = 0 To 6
        Cells(1, i + 1).Select
        Do
            Selection.End(xlDown).Select
        Loop While (Selection.row  65536)
        Selection.End(xlUp).Select
        LastRow(i) = Selection.row
    Next
End Sub

I do not get the compile error with this Sub. In fact, I use the array in another sub in 2 places, and it doesn't cause a problem. Where I get the problem is later on in this (unfisnished) Sub.
Code:

Sub ExportToTxt()
    Dim row As Long
    Dim col, LastRow As Integer
    Dim fnum, fldr, fileName As String
    Dim WshShell As New WshShell
    Set WshShell = CreateObject("WScript.Shell")
    fldr = WshShell.SpecialFolders("MyDocuments")
    fileName = fldr & "test.txt"
    fnum = FreeFile()
    Call GetLastRows
    Open fileName For Output As fnum
    For col = NPANXX_OUT To COMNTY_OUT
        If (LastRow(col - 1) > 1) Then
        Print #fnum, "("
        Print #fnum,
            For row = 2 To LastRow
                Print #fnum, Me.Cells(row, col).Value
            Next
        Print #fnum, ")"
    Next
    Close #fnum
End Sub

It must be my lack of experience with VBA showing, but I can't for the life of me figure out why the compiler is hanging here. Any help?

Is there a way to copy a value from one cell to another, without using select? Same workbook, different worksheet. This line is causing an error "Expected array". lstrw is the lastrow and is an int.


For i = 0 To (lstrw)
sheets("Output").Range("A1").Offset(i, 0).Value = sheets("Site2").Range("A1").Offset(i, 0).Value
Next i
End Sub



Thanks again,

I need a formula for the following:

Year 1 I want to save 500$.
Year 2 I want to increase this saving with 10%
Year 3 I want to increase this saving with 9%
Year 4 I want to increase this saving with 8%
Year 5 I want to increase this saving with 7%
Year 6 I want to increase this saving with 6%
Year 7 I want to increase this saving with 5%
Year 8 I want to increase this saving with 4%
Year 9 I want to increase this saving with 3%
Year 10 I want to increase this saving with 2%
Year 11 I want to increase this saving with 1%
Year 12 I want to maintain previous year's saving

I'm going to have 6% return on my savings each year.

How much will I have saved after 20 years including annual return?

Thanks!

I have a list like this:

Sheet1

  A B C 1 Alfa Status D 75 2 Bravo Status A   3 Charlie Status B 483 4 Delta Status B 78 5 Echo Status C   6 Foxtrot Status A   7 Golf Status B   8 Hotel Status B 12 9 India Status A   10 Juliett Status A 405 11 Kilo Status C   12 Lima Status B   13 Mike Status A   14 November Status B 27 15 Oscar Status B 13 16 Papa Status B   17 Quebec Status C 140 18 Romeo Status B 426 19 Sierra Status C 484 20 Tango Status D 197

Excel tables to the web >> Excel Jeanie HTML 4

I want to single out all Status B values, and put them in a new table in descending order.

In this case, it would look like this:

Sheet1

  A B C 10 Charlie Status B 483 11 Romeo Status B 426 12 Delta Status B 78 13 November Status B 27 14 Oscar Status B 13 15 Hotel Status B 12

Excel tables to the web >> Excel Jeanie HTML 4

So I need to ignore blank values, determine which row has the highest quantity, and then return the value in column A and column C.

I know I can use array formulas to a certain extent, but I'm stumped as to how to store the row of the highest values in the array while ignoring blanks. Any ideas?

I keep getting a "Compile Error - Expected Array" error message at

prompt = "How many text lines will unique product number " & ModifyUniqueProduct & " have?"
Caption = "Modify Customer Order Information"
Default = 1
ReDim ModifyNumberofProductLines(Val(InputBox(prompt, Caption, Default)) - 1)

What have I coded incorrectly?

Thanks!

Code:

Sub ModifyOrder()

Dim Default As Integer
Dim ModifyUniqueProduct As Integer
Dim ModifyProductMaterial As Integer
Dim ModifyProductQuantity As Integer
Dim ModifyNumberofProductLines As Integer
Dim ModifyCharactersPerLine(9) As Variant
Dim ModifyCharacterHeight(9) As Variant
Dim ModifySymbolQuantity(9) As Variant
Dim ModifySymbolWidth(9) As Variant
Dim ModifyGraphicWidth(9) As Variant
Dim ModifySymbolHeight(9) As Variant
Dim ModifyTextLineSpacing(8) As Variant




        prompt = "Which product number would you like to modify?"
        Caption = "Modify Customer Order Information"
        ModifyUniqueProduct = Val(InputBox(prompt, Caption))


        prompt = "Input the material to be used for unique product number " & ModifyUniqueProduct & " ('OB', 'Mylar' or 'Mag')"
        Caption = "Modify Customer Order Information"
        ModifyProductMaterial = InputBox(prompt, Caption)
    
    
        prompt = "Input the customer order quantity for unique product number " & ModifyUniqueProduct
        Caption = "Modify Customer Order Information"
        ModifyProductQuantity = Val(InputBox(prompt, Caption))
    
    
        prompt = "How many text lines will unique product number " & ModifyUniqueProduct & " have?"
        Caption = "Modify Customer Order Information"
        Default = 1
        ReDim ModifyNumberofProductLines(Val(InputBox(prompt, Caption, Default)) - 1)
    
        For w = 0 To UBound(ModifyNumberofProductLines)
            prompt = "How many characters (including spaces) will text line" & w + 1 & " have?"
            Caption = "Modify Customer Order Information"
            Default = 1
            ModifyCharactersPerLine(w) = Val(InputBox(prompt, Caption, Default))
         
            prompt = "Input the height of the characters for line" & w + 1
            Caption = "Modify Customer Order Information"
            Default = 1
            ModifyCharacterHeight(w) = Val(InputBox(prompt, Caption, Default))
         
            prompt = "Input the number of symbols that will appear on text line" & w + 1
            Caption = "Modify Customer Order Information"
            Default = 0
            ModifySymbolQuantity(w) = Val(InputBox(prompt, Caption, Default))
         
            prompt = "Input the maximum width of the symbol that will appear on text line" & w + 1
            Caption = "Modify Customer Order Information"
            Default = 0
            ModifySymbolWidth(w) = Val(InputBox(prompt, Caption, Default))
         
            prompt = "Input the width of any graphic or logo that is adjacent to to text line" & w + 1
            Caption = "Modify Customer Order Information"
            Default = 0
            ModifyGraphicWidth(w) = Val(InputBox(prompt, Caption, Default))
         
            prompt = "Input the height of the symbol on text line" & w + 1
            Caption = "Modify Customer Order Information"
            Default = 0
            ModifySymbolHeight(w) = Val(InputBox(prompt, Caption, Default))
         
            If w >= 1 Then
         
                prompt = "Input the Spacing (in inches) between text line" & w & " and text line" & w + 1
                Caption = "Modify Customer Order Information"
                Default = 0.5
                ModifyTextLineSpacing(w) = Val(InputBox(prompt, Caption, Default))
            
            End If
            
       Next w
            
       
       Range("C8").Offset(ModifyUniqueProduct - 1, 0).Value = ModifyProductMaterial
       Range("D8").Offset(ModifyUniqueProduct - 1, 0).Value = ModifySProductQuantity
            
       For tt = 0 To UBound(ModifyNumberofProductLines)
            Range("C36").Offset(t, 0).Value = ModifyCharactersPerLine(tt)
            Range("D36").Offset(t, 0).Value = ModifyCharacterHeight(tt)
            Range("F36").Offset(t, 0).Value = ModifySymbolQuantity(tt)
            Range("G36").Offset(t, 0).Value = ModifySymbolWidth(tt)
            Range("H36").Offset(t, 0).Value = ModifyGraphicWidth(tt)
            Range("F61").Offset(t, 0).Value = ModifySymbolHeight(tt)
       Next t

       Range("D62").Offset(ModifyUniqueProduct - 1, 0).Value = TextLineSpacing
       
   
'
End Sub