Convert excel formula to vba code

Topic

New Reply

Hello all,

First time posting so I apologies in advance if I break any forum rules or I don’t explain myself to well.

I have a formula that I use to pull data off query table and sort it based on a cell value above it. At the moment it works as is but I see it as a sloppy way of getting it done and hope that there was a way to change to vba code. The formula in question is below. It basically have it look for a match and exclude anything I have on a list and display the rest. I would love to shorten this up in VB and have it apply to a certain range then to have this code in every cell.

=IFERROR(INDEX(‘Raw Data’!$C$2:$C$5000,SMALL(IF((‘Raw Data’!$A$2:$A$5000=B$1)*(‘Raw Data’!$B$2:$B$5000″”)*(IF((‘Raw Data’!$B$2:$B$5000=$AA$2)+(‘Raw Data’!$B$2:$B$5000=$AA$3)+(‘Raw Data’!$B$2:$B$5000=$AA$4)+(‘Raw Data’!$B$2:$B$5000=$AA$5)+(‘Raw Data’!$B$2:$B$5000=$AA$6)+(‘Raw Data’!$B$2:$B$5000=$AA$7)+(‘Raw Data’!$B$2:$B$5000=$AA$8)+(‘Raw Data’!$B$2:$B$5000=$AA$9)+(‘Raw Data’!$B$2:$B$5000=$AA$10)+(‘Raw Data’!$B$2:$B$5000=$AA$11)+(‘Raw Data’!$B$2:$B$5000=$AA$12)+(‘Raw Data’!$B$2:$B$5000=$AA$13)+(‘Raw Data’!$B$2:$B$5000=$AA$14)+(‘Raw Data’!$B$2:$B$5000=$AA$15)+(‘Raw Data’!$B$2:$B$5000=$AA$16)=1,0,1)),ROW(‘Raw Data’!$C$2:$C$5000)-ROW(‘Raw Data’!$C$2)+1),ROWS(‘Raw Data’!$C$2:’Raw Data’!$C2))),””)

I would appreciate any advice or help as I’m true novice and have hit a wall.

Sample
40444-Report

Reply | Quote

Replies

Hi

Welcome to the Lounge.

The quoted formula is an array formula.
This is an Excel2013 file – for others looking at this in Excel2010 or 2007, in the vba code you will need to replace FullSeriesCollection object with SeriesCollection

zeddy

Reply | Quote

Record Macro ??

Code:

Sub Macro2()

    ActiveCell.FormulaR1C1 = _
        “=IFERROR(INDEX(‘Raw Data’!R2C3:R5000C3,SMALL(IF((‘Raw Data’!R2C1:R5000C1=R1C[-2])*(‘Raw Data’!R2C2:R5000C2″”””)*(IF((‘Raw Data’!R2C2:R5000C2=R2C27)+(‘Raw Data’!R2C2:R5000C2=R3C27)+(‘Raw Data’!R2C2:R5000C2=R4C27)+(‘Raw Data’!R2C2:R5000C2=R5C27)+(‘Raw Data’!R2C2:R5000C2=R6C27)+(‘Raw Data’!R2C2:R5000C2=R7C27)+(‘Raw Data’!R2C2:R5000C2=R8C27)+(‘Raw Data’!R2C2:R5000C2=R” & _
        “Raw Data’!R2C2:R5000C2=R10C27)+(‘Raw Data’!R2C2:R5000C2=R11C27)+(‘Raw Data’!R2C2:R5000C2=R12C27)+(‘Raw Data’!R2C2:R5000C2=R13C27)+(‘Raw Data’!R2C2:R5000C2=R14C27)+(‘Raw Data’!R2C2:R5000C2=R15C27)+(‘Raw Data’!R2C2:R5000C2=R16C27)=1,0,1)),ROW(‘Raw Data’!R2C3:R5000C3)-ROW(‘Raw Data’!R2C3)+1),ROWS(‘Raw Data’!R2C3:’Raw Data’!R[-1]C3))),””””)”
    Range(“D4”).Select
End Sub

Reply | Quote

Pako,

This code will build your columns in the “Intervention Rate” sheet based on the data in the “Raw Data” sheet and the exceptions in your exceptions list. I assumed that the exceptions in the list would be the names of Stock. Click on the “Get Data” button to populate the “Intervention Rate”. The code uses 2 helper columns (H and G) on the Raw Data sheet that can be hidden if desired. You can add additional names and unlimited rows in the “Raw Data” sheet. Also note, no formulas are in the cells.

HTH,
Maud

Code:

Public Sub IndexVals()
[COLOR=”#008000″]’————————————————-
‘DECLARE AND SET VARIABLES[/COLOR]
Dim ws1 As Worksheet, ws2 As Worksheet
Set ws1 = Worksheets(“Intervention Rate”)
Set ws2 = Worksheets(“Raw Data”)
LastRow = ws2.Cells(Rows.Count, 1).End(xlUp).Row
Unique = WorksheetFunction.CountIf(ws2.Range(“F2:F5000”), “Unique”)
ws2.Range(“G2:G5000”).ClearContents
ws1.Range(“A1:K5000″).ClearContents
[COLOR=”#008000”]’————————————————-
‘CREATE HEADER WITH UNIQUE NAMES[/COLOR]
col = 2
For I = 2 To 5000
    If ws2.Cells(I, “F”) = “Unique” Then
        ws1.Cells(1, col) = ws2.Cells(I, 1)
        col = col + 2
    End If
Next I
[COLOR=”#008000″]’————————————————-
‘GET DATA[/COLOR]
Row = 2
For J = 2 To Unique * 2 Step 2
    For I = 2 To LastRow
        ws1.Cells(Row, 1) = Row – 1
        If ws2.Cells(I, 1) = ws1.Cells(1, J) And ws2.Cells(I, “G”)  “X” And _
            CheckException(ws2.Cells(I, “B”)) Then
            ws2.Cells(I, “G”) = “X”
            ws1.Cells(Row, J) = ws2.Cells(I, 3)
            ws1.Cells(Row, J + 1) = ws1.Cells(Row, 1) / ws1.Cells(Row, J)
            Row = Row + 1
        End If
    Next I
    Row = 2
Next J
End Sub


Public Function CheckException(exception As String) As Boolean
For I = 2 To 16
    If Range(“AA” & I) = exception Then
        CheckException = False
        Exit Function
    End If
    CheckException = True
Next I
End Function

Reply | Quote

Thanks for your great info…. very approachable.

Reply | Quote

This is an Excel2013 file – for others looking at this in Excel2010 or 2007, in the vba code you will need to replace FullSeriesCollection object with SeriesCollection

Like Zeddy stated, you will have to make the above changes to work outside of Excel 2013

Reply | Quote

Maudibe,

It’s perfect. It does everything I needed and it’s so efficient. Using my old setup it took forever for all the calculations to complete and now with your method its borderline instant. I seriously want to apply this to as many of my reports as I can.

Thank you so much for taking the time and teaching usme this method. I am indebted to you!

Reply | Quote