Results 1 to 7 of 7
  1. #1
    New Lounger
    Join Date
    May 2015
    Posts
    2
    Thanks
    1
    Thanked 0 Times in 0 Posts

    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
    Last edited by Pako; 2015-05-01 at 12:46.

  2. #2
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,822
    Thanks
    134
    Thanked 481 Times in 458 Posts
    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
    Last edited by zeddy; 2015-05-01 at 13:38.

  3. #3
    3 Star Lounger
    Join Date
    Nov 2011
    Location
    Australia
    Posts
    221
    Thanks
    80
    Thanked 3 Times in 2 Posts
    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. #4
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,633
    Thanks
    115
    Thanked 647 Times in 590 Posts
    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
    '-------------------------------------------------
    'CREATE HEADER WITH UNIQUE NAMES
    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
    Attached Files Attached Files

  5. #5
    New Lounger
    Join Date
    Oct 2014
    Posts
    9
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thanks for your great info.... very approachable.

  6. #6
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,633
    Thanks
    115
    Thanked 647 Times in 590 Posts
    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. #7
    New Lounger
    Join Date
    May 2015
    Posts
    2
    Thanks
    1
    Thanked 0 Times in 0 Posts
    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!

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •