Add Xlookup to a range via Excel VBA

Question

I tried searching for a solution to my XLookup via VBA problem but I couldn't find one. I have this below 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.

Hi @BigBen , I'm not really good at the coding stuff. If you can help me with the full code, I'll appreciate that. — ram singh, Sep 03 '20 at 13:05
Hi @ForwardEd , When the cell is BLANK, my cell shows "00-Jan-00" because it's in date format. So to make it go away, I have added "" in my formula. So yes, both are different. "" is not technically a BLANK. — ram singh, Sep 03 '20 at 13:10

Dick Kusleika · Accepted Answer · 2020-09-11T10:57:00.293

0

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.

edited Sep 11 '20 at 10:57

answered Sep 03 '20 at 13:36

Dick Kusleika

32,673
4
52
73

Hi, it helped for 5 rows of data but when I extended my data to 50 rows and one more set of data of 250 rows, it stopped working. When I delete cell values, instead of formula that should appear, it stays blank. Any idea? – ram singh Sep 07 '20 at 08:15
You have to change the C2:C6 part of the code to cover whatever range you want to be affected by this. For example, if you want the range C2:C51 to have the formula, change the code to use that range. – Dick Kusleika Sep 08 '20 at 13:34
Oh trust me I did. But it still didn't work. When I changed the range to 250, It added formula only till row 179. Don't know why. Someone told me that it's because of the event handler (I don't have any clue as to what it is). So that's why I came up with below code which seems to be working. I may have messed up your code. Anyways, thanks a lot for helping me out. Really appreciate your time. – ram singh Sep 10 '20 at 05:04
Oh yeah, rookie mistake on my part. I'm changing a cell within a Change Event Handler without disabling events. That means the change I'm making in the code triggers that same code to run again and depending on how many blanks there are, it can run out of stack space and just stop. I'll adjust my code. – Dick Kusleika Sep 11 '20 at 10:56

ram singh · Answer 2 · 2020-09-07T13:19:36.453

This one seems to be working for any set of data. Thanks to everyone for the help:

Private Sub InsertFormula()

Dim mwRng As Range
    Set mwRng = Range("C2:C250")
    Dim d As Range
    For Each d In mwRng
        If d.Value = "" Then
      d.Formula = "=IF(RC[-1]="""",""-"",INDEX(C[1],MATCH(RC[-1],C[-2],0)))"
    End If
Next d

End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Me.Range("C2:C250")) Is Nothing Then
        Application.EnableEvents = False
        Call InsertFormula
        Application.EnableEvents = True
    End If
End Sub

Add Xlookup to a range via Excel VBA

2 Answers2