2

I'm really stumped with converting Index + Match to VBA. I'm very new to VBA, this is proving to be beyond my abilities.

I have a table in Sheet2 With Columns, 'Case', 'Probability', 'Impact' & 'Severity'. Then a Matrix in Sheet1

My formula (filled down the column) is:

=IFNA(INDEX(Sheet1!$C$2:$G$6,MATCH([@Probability],Sheet1!$B$2:$B$6,0),MATCH([@Impact],Sheet1!$C$1:$G$1,0)),"")

I'm trying to auto-populate 'Severity' in the table based on the values in the Matrix

Table

Matrix

I tried using Application.WorksheetFunction but I don't get any results.

Any advice would be much appreciated.

VBasic2008
  • 44,888
  • 5
  • 17
  • 28
DomDomDom
  • 23
  • 3
  • What is the Worksheet and Table names? Do you want to return the Range of the filtered row? – TinMan Feb 27 '22 at 16:42

1 Answers1

1

VBA Using INDEX/MATCH Formula

  • These will populate the values instead of the formulas.
  • If you remove the line .Value = .Value, the formulas stay.
  • Adjust the worksheet and table names.
Option Explicit

Sub TestEdu()
    
    Dim wb As Workbook: Set wb = ThisWorkbook ' workbook containing this code
    Dim ws As Worksheet: Set ws = wb.Worksheets("Sheet2") ' adjust
    Dim tbl As ListObject: Set tbl = ws.ListObjects("Table1") ' adjust
    Dim lcl As ListColumn: Set lcl = tbl.ListColumns("Severity")
    
    With lcl.DataBodyRange
        .Formula = "=IFNA(INDEX(Sheet1!$C$2:$G$6,MATCH([@Probability]," _
            & "Sheet1!$B$2:$B$6,0),MATCH([@Impact],Sheet1!$C$1:$G$1,0)),"""")"
        .Value = .Value
    End With

End Sub

Sub TestCompact()
    With ThisWorkbook.Worksheets("Sheet2").ListObjects("Table1") _
            .ListColumns("Severity").DataBodyRange
        .Formula = "=IFNA(INDEX(Sheet1!$C$2:$G$6,MATCH([@Probability]," _
            & "Sheet1!$B$2:$B$6,0),MATCH([@Impact],Sheet1!$C$1:$G$1,0)),"""")"
        .Value = .Value
    End With
End Sub
VBasic2008
  • 44,888
  • 5
  • 17
  • 28
  • Instead of manually running the Marco is it possible for the code to run if columns 'Probability' 'Impact' are populated? I've tried Set Rng = Range(tbl & "[[Impact]:[Probability]]").Select For Each cell In Rng If cell.Value <> "" Then and Set Rng = Range("tbl[Impact]:tbl[Probability]").Select For Each cell In Rng If cell.Value <> "" Then – DomDomDom Feb 28 '22 at 15:28
  • I guess that's out of scope. Ask another question, copy this code to it and describe what it does and what you want it to do. – VBasic2008 Feb 28 '22 at 18:22