Add Formula to a range via VBA

[PankajMaheshwari]

Hi Experts,

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(B2="","",XLOOKUP(B2,A:A,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.

 

[mehidy1437]

Hope some experts will look into this.

 

[PankajMaheshwari]

Hi Mehidy1437, your solution works but what I'm trying to do is automate this. So I don't want every time to go and run this code. It should run automatically. I'm not sure if that's possible or not.

 

[PankajMaheshwari]

This is my final code which seems to be working. Thanks for the help though.

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

 

[mehidy1437]

My code is not good enough for the task, that's why I have removed.
Some experts will look into this for sure.