Search code examples
excelvbaif-statementexcel-formulaxlookup

Add Xlookup to a range via Excel VBA

I tried searching for a solution to my XLookup via VBA problem but I couldn't find one. I have this below data set:

Data Set

In the Data Set, If any cell in the range C2:C6 is blank, I want to use this formula =IF(ISBLANK(B2),"",XLOOKUP(B2,A:A,IF(ISBLANK(D:D),"",D:D))) in those cells. Where row number of B2 is variable depending upon the row we are putting this formula via VBA.

If any cell in the range C2:C6 has value, I want to use that value without any formula. And if someone deletes the value and the cell becomes blank, VBA will add above formula to that cell.

Currently in the screenshot above, all the cells in range C2:C6 has above formula.

I hope I made sense. If this is not doable, it's okay. I can always use a helper column. But I think VBA would be a more cleaner way for my Dashboard.

Many Thanks in Advance.

Solution

  • In the sheet's class module, put this code

    Private Sub Worksheet_Change(ByVal Target As Range)
    
    Dim rCell As Range
    
    For Each rCell In Me.Range("C2:C6").Cells
        If IsEmpty(rCell.Value) Then
            Application.EnableEvents = False
            rCell.FormulaR1C1 = "=IF(ISBLANK(RC[-1]),"""",xlookup(RC[-1],C[-2],IF(ISBLANK(C[1]),"""",C[1])))"
            Application.EnableEvents = True
        End If
    Next rCell
    
End Sub

    This will run every time something on the sheet changes. That can't slow things down so you don't want to try to do too much in the Change event. It does not fire on calculate, though.