Excel Tips -Modify Array Formula

I'm creating a worksheet using excel 2007. In column A I've calendar weeks from 1 to 52. In column E I'm using data validation to select an entry from the list. Now in order to do some calculation in column N, I've written an array formula. I'm trying to implement the following logic using this formula -" =IF(AND($A$2:$A2=A3,MATCH(E3,E:E,0)=ROW()),(100-((SUM($M$2:$M3)*100))/100),INDEX(N$2:N2,SMALL(IF(E$2:E3=E3,ROW(E$2:E3)),COUNTIF(E$2:E3,E3)-1)-1,1)-M3)".

For N3:
IF(A3=A2) && the occurrence of an entry in E3 is for the first time THEN (N3=((100-((M2+M3)*100))/100))
ELSE N3=Nn - M3.

Here, Nn denotes the last occurrence of the an entry displayed E3.

For N4:

IF(A4=A3 & A3=A2) && the occurrence of an entry in E4 is for the first time THEN (N4=((100-((M2+M3+M4)*100))/100))
ELSE N4=Nn - M4.

Similarly,
For N5:

IF(A5=A4 & A4=A3& A3=A2) && the occurrence of an entry in E5 is for the first time THEN (N5= ((100-(M2+M3+M4+M5)*100))/100))
ELSE N5=Nn - M5.

and so on...



But there seems to be a problem. In E2 I've selected an entry, the result is fine and it shows the correct result 75%. But in E3, If I select a different entry other than the one selected in E2, then the result in N3 is diplayed as 50%.
This is incorrect as according to the calculation it should show 75%. The result 50% in N3 is correct only if E2=E3.

I've attached the excel for reference. Could you please check and tell me what's wrong with my formula?

This is part of the full code

if resize(7), "abc" would copy down 7 times right?
is there a way to make it like, array("abc", "def")
then it'll copy down: abc, def, abc, def, abc, def,abc for resize(7)

without making it too complicated?

Code:

 
        With newWB2.Sheets("sheet2")
        .Cells(7, Columns.Count).End(xlToLeft).Offset(0, 1).Resize(7) = _
        WorksheetFunction.Transpose(Array("abc"))
        End With

I'm creating a worksheet using excel 2007. In column A I've calendar weeks from 1 to 52. In column F I'm using user form to select an entry from the list. Now in order to do some calculation in column J, I've written an array
formula. I'm trying to implement the following logic using this formula:

1st condition:
if the value in column A matches with the value of column A in previous row;
Condition passes....
Then check
if the value in Column F has occurred for the 1st time for similar value of column A
Condition passes....
Then, the value in Jx = ((20-Hx)/20)

2nd Condition:
if the value in column A is not equal to the value of column A in previous row;
Condition passes.....
Then , the value in Jx = ((20-Hx)/20)

Jx and Hx could be J2, H2 or J3, H3 etc. depending upon the calculation.

3rd Condition:
if the value in column A matches with the value of column A in previous row;
Condition passes....
Then check
if the value in column F has occured earlier for similar value of column A
Condition passes.....
Then, the value in Jx = Jy-(Ix*100)

Jy denotes the value of J where the Fx has occured last time.


This is the formula which goes in J5. But it throws #NUM error.

=IF(AND($A$2:$A4=A5,MATCH(F5,F:F,0)=ROW()),((20-H5)/20),INDEX(J$2:J4,SMALL(IF(F$2:F5=F5,ROW(F$2:F5)),COUNTIF(F$2:F5,F5)-1)-1,1)-I5)

I've also attached the excel for reference.

I'm creating a worksheet using excel 2007. In column A I've calendar weeks from 1 to 52. In column F I'm using user form to select an entry from the list. Now in order to do some calculation in column J, I've written an array
formula. I'm trying to implement the following logic using this formula:

1st condition:
if the value in column A matches with the value of column A in previous row;
Condition passes....
Then check
if the value in Column F has occurred for the 1st time for similar value of column A
Condition passes....
Then, the value in Jx = ((20-Hx)/20)

2nd Condition:
if the value in column A is not equal to the value of column A in previous row;
Condition passes.....
Then , the value in Jx = ((20-Hx)/20)

Jx and Hx could be J2, H2 or J3, H3 etc. depending upon the calculation.

3rd Condition:
if the value in column A matches with the value of column A in previous row;
Condition passes....
Then check
if the value in column F has occured earlier for similar value of column A
Condition passes.....
Then, the value in Jx = Jy-(Ix*100)

Jy denotes the value of J where the Fx has occured last time.


This is the formula which goes in J5. But it throws #NUM error.

=IF(AND($A$2:$A4=A5,MATCH(F5,F:F,0)=ROW()),((20-H5)/20),INDEX(J$2:J4,SMALL(IF(F$2:F5=F5,ROW(F$2:F5)),COUNTIF(F$2:F5,F5)-1)-1,1)-I5)

Hi,
In my excel code, i need to enter an array formula into cell and autofill the formula upto the last row.
the formula is as below
Code:

Selection.FormulaArray = _
        "=COUNTIF(RC[-106]:RC[-2],'[RNDCIQ Check V70.xlsm]Input'!R2C5:R68C5)"

How can I modify the formula , so that the workbook name "RNDCIQ Check V70.xlsm" is replaced by a variable RNDtool (RNDtool holds the value of ActiveWorkbook.name)?

Also, I need to modify "Input'!R2C5:R68C5". It should be R2C5 to last non empty row.

Hi,
In my excel code, i need to enter an array formula into cell and autofill the formula upto the last row.
the formula is as below
VB:

Selection.FormulaArray = _ 
"=COUNTIF(RC[-106]:RC[-2],'[RNDCIQ Check V70.xlsm]Input'!R2C5:R68C5)" 

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



How can I modify the formula , so that the workbook name "RNDCIQ Check V70.xlsm" is replaced by a variable RNDtool?
Also, I need to modify "Input'!R2C5:R68C5". It should be R2C5 to last non empty row.

Hello everyone,

I am looking for some feedback on the code I have written. It is my first attempt at using arrays in code for the purpose of accumulating data, and subesequently using the same data to create a report.

My code below looks at a table that is 10 columns wide and reads all of the data into 10 different arrays - this is where feedback may be useful, should I have used a multi dimensional array (if so show me how please).

After it reads the data into the arrays it looks at the first array and finds the unique items in that array, which are then used to create a nice report outlined by the groupings.

Once again any feedback on ways to improve the code would be great - learning lots.

Thanks

Code:

Option Explicit
Sub investment1()
'Macro to create investment portfolio report - By Gerry on March 9, 2009
    Dim lr As Long, i As Long, j As Long, k As Long, l As Long
    Dim CUname As String, RPdate As String
    Dim grp1() As Variant, grp2() As Variant, grp3() As Variant, grp4() As Variant
    Dim grp5() As Variant, grp6() As Variant, grp7() As Variant, grp8() As Variant
    Dim grp9() As Variant, grp10() As Variant
    Dim hdg() As Variant, hdg1 As Long
    Application.ScreenUpdating = False
'Get the relevant data - 10 columns of data, 1 array for each column
    With Worksheets("Inputs")
        lr = .Cells(.Rows.Count, 1).End(xlUp).Row
        ReDim grp1(1 To lr), grp2(1 To lr), grp3(1 To lr), grp4(1 To lr), grp5(1 To lr)
        ReDim grp6(1 To lr), grp7(1 To lr), grp8(1 To lr), grp9(1 To lr), grp10(1 To lr)

        For i = 8 To lr
        
            grp1(i) = .Cells(i, 1).Value
            grp2(i) = .Cells(i, 2).Value
            grp3(i) = .Cells(i, 3).Value
            grp4(i) = .Cells(i, 4).Value
            grp5(i) = .Cells(i, 5).Value
            grp6(i) = .Cells(i, 6).Value
            grp7(i) = .Cells(i, 7).Value
            grp8(i) = .Cells(i, 8).Value
            grp9(i) = .Cells(i, 9).Value
            grp10(i) = .Cells(i, 10).Value
        Next
    'Get the CU name and report date
        CUname = .Range("A1").Value
        RPdate = Format(.Range("C5").Value, "MMMM DD, YYYY")
    End With

'Get the category headings and count using custom function called UniqueItems
        hdg = UniqueItems(grp1, False)
        hdg1 = UniqueItems(grp1)

Sheets.Add
ActiveSheet.Name = "newSht"


'Set Starting row for report
    k = 7
'Put the data into report format
    For i = 1 To hdg1 'counter for each group heading
        Cells(k, 1).Value = hdg(i)
        Cells(k, 4).Value = "Start"
        Cells(k, 5).Value = "Maturity"
        Cells(k, 6).Value = "Face Value"
        Cells(k, 7).Value = "Amortization"
        Cells(k, 8).Value = "Book Value"
        Cells(k, 9).Value = "Yield"
        Cells(k, 10).Value = "Bond Rating"
        l = k 'Counter for top row of group
            For j = 1 To UBound(grp1) 'search array for data that matches heading
                If grp1(j) = hdg(i) Then
                        k = k + 1
                        Cells(k, 2).Value = grp2(j)
                        Cells(k, 3).Value = grp3(j)
                        Cells(k, 4).Value = grp4(j)
                        Cells(k, 5).Value = grp5(j)
                        Cells(k, 6).Value = grp6(j)
                        Cells(k, 7).Value = grp7(j)
                        Cells(k, 8).Value = grp8(j)
                        Cells(k, 9).Value = grp9(j)
                        Cells(k, 10).Value = grp10(j)
                End If
            Next j
        'Insert Totals
            k = k + 2
            l = k - l - 1
                Cells(k, 1).Value = hdg(i) & " Total"
                Cells(k, 6).FormulaR1C1 = "=SUBTOTAL(9,R[-" & l & "]C:R[-1]C)"
                Cells(k, 7).FormulaR1C1 = "=SUBTOTAL(9,R[-" & l & "]C:R[-1]C)"
                Cells(k, 8).FormulaR1C1 = "=SUBTOTAL(9,R[-" & l & "]C:R[-1]C)"
                Cells(k, 9).FormulaR1C1 = "=sumproduct(--(R[-" & l & "]C[-3]:R[-1]C[-3]),--(R[-" & l & "]C:R[-1]C))/RC[-3]"
                
        'Format display
                With Union(Range(Cells(k, 1), Cells(k, 10)), Range(Cells(k - l - 1, 1), Cells(k - l - 1, 10)))
                    .Font.Bold = True
                    With .Borders(xlEdgeTop)
                        .LineStyle = xlContinuous
                        .Weight = xlThin
                        .ColorIndex = xlAutomatic
                    End With
                    With .Borders(xlEdgeBottom)
                        .LineStyle = xlContinuous
                        .Weight = xlThin
                        .ColorIndex = xlAutomatic
                    End With
                End With
          'Put two blank rows in between
            k = k + 3
        Next i

'Finish Formatting - make it look pretty
    Range("F:H").NumberFormat = "_(* #,##0_);_(* (#,##0);_(* ""-""??_);_(@_)"
    Range("I:I").NumberFormat = "0.00%"
    Columns.AutoFit
    Columns("A:A").ColumnWidth = 4
    Range("A1").Value = CUname
    Range("A2").Value = "Board Report on Investment Management"
    Range("A3").Value = "Part 4 - Investment Quality"
    Range("A4").Value = RPdate
        With Range("A1:J4")
            .HorizontalAlignment = xlCenterAcrossSelection
            .Font.Bold = True
        End With


ActiveWindow.DisplayGridlines = False
Application.ScreenUpdating = True
End Sub

I do not know the area of memory or VBA code to call where all instances can use a common number array. Just 10 numbers used as flags that would be set to a 1 or a 0. All instances being able to read and modify those 10 numbers.


Any sample code to get me going in the right direction should do it.


Thanks.


Chuck

Hi,
i have list in column"E" and "G" and excelformula to get result in column"F" ,what change do i have to make to the formula if i have lists in column"E","G","H" or more, and get the same result?
thanks in advance for any help or suggestions.
here an example;
Sheet2

E F G 1 List1 List2 2 01 02 03 04 05 , 06 07 08 09 10 ,11 12 13 14 15 16 17 18 19 20 ,21 22 23 24 25 ,26 27 28 29 30 ,31 32 33 34 35 ,36 37 38 39 40 ,41 42 43 44 45 15 16 24 34 36 ,26 27 28 29 30 ,41 42 43 44 45 ,21 22 23 24 25 ,31 32 33 34 35 ,41 42 43 44 45 3 01 02 03 04 05 , 06 07 08 09 10 ,11 17 25 35 37 15 17 22 26 45 ,31 32 33 34 35 ,36 37 38 39 40 ,21 22 23 24 25 ,36 37 38 39 40 ,41 42 43 44 45 4 01 02 03 04 05 , 06 07 08 09 10 ,11 18 23 27 41 15 30 32 37 41 ,16 17 18 19 20 ,21 22 23 24 25 ,26 27 28 29 30 ,31 32 33 34 35 ,36 37 38 39 40 5 01 02 03 04 05 , 06 07 08 09 10 ,11 26 33 38 42 16 17 18 19 20 ,21 22 23 24 25 ,26 27 28 29 30 ,31 32 33 34 35 ,36 37 38 39 40 ,41 42 43 44 45 6 01 02 03 04 05 , 06 07 08 09 10 ,12 18 21 31 38 16 17 18 19 20 ,21 22 23 24 25 ,31 32 33 34 35 ,26 27 28 29 30 ,31 32 33 34 35 ,41 42 43 44 45 7 01 02 03 04 05 , 06 07 08 09 10 ,12 19 24 28 42 16 17 18 19 20 ,21 22 23 24 25 ,36 37 38 39 40 ,26 27 28 29 30 ,36 37 38 39 40 ,41 42 43 44 45
Spreadsheet Formulas Cell Formula F2 {=LOOKUP("a",IF ({1,0},"",INDEX ($G$2:$G$7,MATCH (TRUE,MMULT (--ISNUMBER (FIND (" "&RIGHT ("0"&{1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,32,33,34,35,36,37,38,39,40,41,42,43 ,44,45},2) &" ",SUBSTITUTE (" "&E2&" "&$G$2:$G$7&" ",","," ") ) ) ,{1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1}) =45,0) ) ) )}
E F G H 1 list1 Result List2 List3 2 01 02 03 04 05 , 06 07 08 09 10 ,11 12 13 14 15 16 17 18 19 20 ,21 22 23 24 25 ,26 27 28 29 30 ,31 32 33 34 35 ,36 37 38 39 40 ,41 42 43 44 45 15 16 24 34 36 ,26 27 28 29 30 ,41 42 43 44 45 21 22 23 24 25 ,31 32 33 34 35 ,41 42 43 44 45 3 01 02 03 04 05 , 06 07 08 09 10 ,11 17 25 35 37 15 17 22 26 45 ,31 32 33 34 35 ,36 37 38 39 40 21 22 23 24 25 ,36 37 38 39 40 ,41 42 43 44 45 4 01 02 03 04 05 , 06 07 08 09 10 ,11 18 23 27 41 15 30 32 37 41 ,16 17 18 19 20 ,21 22 23 24 25 26 27 28 29 30 ,31 32 33 34 35 ,36 37 38 39 40 5 01 02 03 04 05 , 06 07 08 09 10 ,11 26 33 38 42 16 17 18 19 20 ,21 22 23 24 25 ,26 27 28 29 30 26 27 28 29 30 ,31 32 33 34 35 ,41 42 43 44 45 6 01 02 03 04 05 , 06 07 08 09 10 ,12 18 21 31 38 16 17 18 19 20 ,21 22 23 24 25 ,31 32 33 34 35 26 27 28 29 30 ,36 37 38 39 40 ,41 42 43 44 45 7 01 02 03 04 05 , 06 07 08 09 10 ,12 19 24 28 42 16 17 18 19 20 ,21 22 23 24 25 ,36 37 38 39 40 31 32 33 34 35 ,36 37 38 39 40 ,41 42 43 44 45
Kind regards
Sezuh

Wondered if there are any Excel macro gurus out there who may be able to help me save a lot of time and finish off this macro I've been struggling on for a financial spreadsheet....

Basically what I'm trying to do is create a flexible business model that you can add new business units to as required.

The top sheet combines all of these individual units together so you can view how your combined business looks.

What I need to do is take the formula that is already present in each cell of the top sheet, and adjust it so that it adds in the new sheet.

So in the VB code for example, I have for one cell:

Range("D92").Select
ActiveCell.FormulaR1C1 = "=FirstUnit!R[377]C+NewUnit!R[377]C"


However this is not good enough as it always uses the same formula each time so when I add the third unit, the formula will not be changed.

What I want it to look like would be as below

Range("D92").Select
ActiveCell.FormulaR1C1 = "=FirstUnit!R[377]C+NewUnit!R[377]C+ThirdUnit!R[377]C"


and then the next time

Range("D92").Select
ActiveCell.FormulaR1C1 = "=FirstUnit!R[377]C+NewUnit!R[377]C+ThirdUnit!R[377]C+FourthUnit!R[377]C"



hopefully that explains what I'm trying to do....

What I need is some way of telling VB to take the formula currently in the cell and then add the extra cell

e.g.

Range("D92").Select
ActiveCell.FormulaR1C1 = EXISTING_FORMULA+"+NewUnit!R[377]C"



Any suggestions? It's a simple example, but once I understand how to get it to take the existing formula and then apply the necessary modification I can apply it as necessary. Cheers