View Full Version : Solved: Input formulas at the bottom of the data vba

Shazam

08-03-2006, 10:15 AM

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.

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

Here is a small attachment below.

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

Shazam

08-03-2006, 01:10 PM

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.

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

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

Shazam

08-03-2006, 04:02 PM

Thank you so much it works perfect!!

I adjust one thing on the second formula.

From this...

MaxRow - 1

To

MaxRow

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

08-04-2006, 10:05 AM

Thanks xld!

I tested it out and it works great.

Thank You!:hi:

Powered by vBulletin® Version 4.2.5 Copyright © 2019 vBulletin Solutions Inc. All rights reserved.