# Thread: Convert excel formula to vba code

1. ## Convert excel formula to vba code

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
Report.xlsm

2. 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

3. 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```

4. 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()
'-------------------------------------------------
'DECLARE AND SET VARIABLES
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
'-------------------------------------------------
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
'-------------------------------------------------
'GET DATA
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```

5. Thanks for your great info.... very approachable.

6. 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

7. ## The Following 2 Users Say Thank You to Maudibe For This Useful Post:

Pako (2015-05-04),XPDiHard (2015-05-07)

8. 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 us\me this method. I am indebted to you!