Solved: Input formulas at the bottom of the data vba

[Shazam]

This is a difficult one.

I'm trying to convert these 2 formulas into vba

In cell B31:

=INDEX($I$1:$I$27,MATCH(LARGE(IF(RIGHT($I$2:$I$28,5)="Total",IF(LEFT($I$2:$ I$28,5)<>"Grand",IF($G$1:$G$27=1,$F$2:$F$28-ROW($F$2:$F$28)/10^5))),ROWS(C$31:C31)),$F$2:$F$28-ROW($F$2:$F$28)/10^5,0))

In cell C31:

=INDEX($F$2:$F$28,MATCH(LARGE(IF(RIGHT($I$2:$I$28,5)="Total",IF(LEFT($I$2:$ I$28,5)<>"Grand",IF($G$1:$G$27=1,$F$2:$F$28-ROW($F$2:$F$28)/10^5))),ROWS(C$31:C31)),$F$2:$F$28-ROW($F$2:$F$28)/10^5,0))

I would like the code to input the formulas at the bottom of the data in column B & C and filldown at least 20 cells below.


My data flucuates daily.



This is what I have so far but its not working.

[VBA]
MaxRow = Range("A65536").End(xlUp).Row - 1
Db = Chr(34)
frmla = "=INDEX(F2:F" & MaxRow & ",MATCH(LARGE(IF(RIGHT(I2:I" & MaxRow & ",5)" _
& "=""Total"",IF(LEFT(I2:I" & MaxRow & ",5)<>""Grand"",IF(G1:G" & MaxRow & "=1" & "," _
& "F2:F" & MaxRow & "-ROW(F2:F" & MaxRow & ")/10^5))),ROWS(L$2:L2)),F2:F" & MaxRow & "" _
& "-ROW(F2:F" & MaxRow & ")/10^5,0))"
Range("B" & MaxRow + 4).FormulaArray = frmla
[/VBA]



Here is a small attachment below.

[Bob Phillips]

[vba]

Sub AddData()
Dim iLastRow As Long
Dim i As Long
Dim iTarget As Long
Dim iStart As Long
Dim rng As Range

iStart = 30
iTarget = 30
iLastRow = Cells(Rows.Count, "I").End(xlUp).Row
For i = 1 To iLastRow
If Cells(i, "G").Value = 1 Then
If InStr(Cells(i, "I").Value, "Total") = 0 Then
Set rng = Range(Cells(iStart, "B"), Cells(iTarget, "B"))
If Application.CountIf(rng, Cells(i, "I").Value) = 0 Then
Cells(iTarget + 1, "B").Value = Cells(i, "I").Value
Cells(iTarget + 1, "C").Value = Cells(i, "F").Value
iTarget = iTarget + 1
End If
End If
End If
Next i

End Sub
[/vba]

[Shazam]

Hi xld,



Thank you for the code xld it works on my example workbook. But not my orginal workbook, my data fluctuates every day and this workbook will be generated daily how can we have it dynamically. Also there might be some data entry errors and the user will change the totals time to time. Do you think we could use the formula I provided above into a vba format? Hopefully it’s possible.

[Bob Phillips]

[vba]

Sub AddData()
Dim MaxRow As Long
Dim Db, frmla

MaxRow = Range("I" & Rows.Count).End(xlUp).Row - 1
Db = Chr(34)
frmla = "=INDEX($I$1:$I$" & MaxRow - 1 & ",MATCH(LARGE(IF(RIGHT($I$2:$I$" & _
MaxRow & ",5)=""Total"",IF(LEFT($I$2:$I$" & MaxRow & ",5)<>""Grand""," & _
"IF($G$1:$G$" & MaxRow - 1 & "=1" & ",$F$2:$F$" & MaxRow & _
"-ROW($F$2:$F$" & MaxRow & ")/10^5))),ROWS($L$" & MaxRow + 4 & _
":L" & MaxRow + 4 & ")),$F$2:$F$" & MaxRow & "-ROW($F$2:$F$" & MaxRow & ")/10^5,0))"
Range("B" & MaxRow + 4).FormulaArray = frmla
frmla = "=INDEX($F$1:$F$" & MaxRow - 1 & ",MATCH(LARGE(IF(RIGHT($I$2:$I$" & _
MaxRow & ",5)=""Total"",IF(LEFT($I$2:$I$" & MaxRow & ",5)<>""Grand""," & _
"IF($G$1:$G$" & MaxRow - 1 & "=1" & ",$F$2:$F$" & MaxRow & _
"-ROW($F$2:$F$" & MaxRow & ")/10^5))),ROWS($L$" & MaxRow + 4 & _
":L" & MaxRow + 4 & ")),$F$2:$F$" & MaxRow & "-ROW($F$2:$F$" & MaxRow & ")/10^5,0))"
Range("C" & MaxRow + 4).FormulaArray = frmla
Range("B" & MaxRow + 4).Resize(, 2).AutoFill Range("B" & MaxRow + 4).Resize(10, 2)
End Sub
[/vba]

What is the purpose of the formula? It seems to get the last item in each unique value. ANd why /10^5?

[Shazam]

Thank you so much it works perfect!!

I adjust one thing on the second formula.

From this...

[vba]MaxRow - 1[/vba]

To

[vba]
MaxRow
[/vba]


Because it was not giving me the largest unique Subtotal value first.



The formula purpose is to get the largest unique subtotal first and then filler downwards.

The /10^5 ??

It makes unique values...

ROW($F$2:$F$398)/10^5 evaluates to:

2/10^5

3/10^5

4/10^5


5/10^5

6/10^5

7/10^5

And so on.

You notice each value is a small fraction which is subtracted from the corresponding value
in Column F. So, for example, if F5 , F6 and F7 each contained 10, and we have the following...

$F$2:$F$398-ROW($F$2:$F$398)/10^5

the first three values evaluate as follows:

10-2/10^5 ---> 9.99998

10-3/10^5 ---> 9.99997

10-3/10^5 ---> 9.99996

resulting in unique values. IF not, match will always get
the first occurrence.

I know I could use helper columns but I prefer a all in one formula.


Hopefully I explained it well because I'm still learning in these kind of arrays.


I will let you know tomorrow when I'm back at work to do further testing.

Thank you for the help.

[Shazam]

Thanks xld!

I tested it out and it works great.

Thank You!