Auto-Populate Formula if entring ID in Cells

[Mohamedazees]

Dear Sir,
I need to popup the formula if I entir the ID in Cell F29 then G20 will popup the Formula if F29 is blank then G29 will become Blank , However I written the below macro but it is not solve my issue please help me to resolve my problem
The Code is below:
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect([G20:R29], Target) Is Nothing Then
Application.EnableEvents = False
Dim cell As Range
For Each cell In Range("F20:F29")
If cell.Value > "" Then
'If cell.Offset(0, 1).Value = "" Then
cell.Offset(0, 1).Value = "=IFERROR(INDEX(Items!$B:$B,MATCH(" & cell.Address & ",Items!$A:$A,0)),0)"
'End If
If cell.Offset(0, 2).Value = "" Then
cell.Offset(0, 2).Value = "=IFERROR(INDEX(Items!$D:$D,MATCH(" & cell.Address & ",Items!$A:$A,0)),0)"
End If
If cell.Offset(0, 3).Value = "" Then
cell.Offset(0, 3).Value = "=IFERROR(INDEX(Items!$E:$E,MATCH(" & cell.Address & ",Items!$A:$A,0)),0)"
End If
If cell.Offset(0, 4).Value = "" Then
cell.Offset(0, 5).Value = "=IFERROR(INDEX(Items!$C:$C,MATCH(" & cell.Address & ",Items!$A:$A,0)),0)"
End If
End If
Next cell
End If
End Sub

 

[Alex Blakenburg]

See if this is heading in the right direction.
Do you really want to test for a change in G-R ? I would have thought you would be testing for a change in F.
In the last formula cell you refer to both columns 4 & 5. I have put column 5, change it if you want 4. Also let me know if you meant to test column 4 before updating column 5.

Private Sub Worksheet_Change(ByVal Target As Range)
    
    Dim cell As Range

    If Not Intersect(Range("G20:R29"), Target) Is Nothing Then
        Application.EnableEvents = False

        For Each cell In Range("F20:F29")
            If cell.Value = "" Then
                cell.Offset(0, 1).Value = ""
                cell.Offset(0, 2).Value = ""
                cell.Offset(0, 3).Value = ""
                cell.Offset(0, 5).Value = ""
            Else
                cell.Offset(0, 1).Value = "=IFERROR(INDEX(Items!$B:$B,MATCH(" & cell.Address & ",Items!$A:$A,0)),0)"
                cell.Offset(0, 2).Value = "=IFERROR(INDEX(Items!$D:$D,MATCH(" & cell.Address & ",Items!$A:$A,0)),0)"
                cell.Offset(0, 3).Value = "=IFERROR(INDEX(Items!$E:$E,MATCH(" & cell.Address & ",Items!$A:$A,0)),0)"
                cell.Offset(0, 5).Value = "=IFERROR(INDEX(Items!$C:$C,MATCH(" & cell.Address & ",Items!$A:$A,0)),0)"
            End If
        Next cell
        Application.EnableEvents = True
    End If
End Sub

 

[Mohamedazees]

Excellent Sir, Working Perfectly Thanks of Lots

See if this is heading in the right direction.
Do you really want to test for a change in G-R ? I would have thought you would be testing for a change in F.
In the last formula cell you refer to both columns 4 & 5. I have put column 5, change it if you want 4. Also let me know if you meant to test column 4 before updating column 5.

Private Sub Worksheet_Change(ByVal Target As Range)
   
    Dim cell As Range

    If Not Intersect(Range("G20:R29"), Target) Is Nothing Then
        Application.EnableEvents = False

        For Each cell In Range("F20:F29")
            If cell.Value = "" Then
                cell.Offset(0, 1).Value = ""
                cell.Offset(0, 2).Value = ""
                cell.Offset(0, 3).Value = ""
                cell.Offset(0, 5).Value = ""
            Else
                cell.Offset(0, 1).Value = "=IFERROR(INDEX(Items!$B:$B,MATCH(" & cell.Address & ",Items!$A:$A,0)),0)"
                cell.Offset(0, 2).Value = "=IFERROR(INDEX(Items!$D:$D,MATCH(" & cell.Address & ",Items!$A:$A,0)),0)"
                cell.Offset(0, 3).Value = "=IFERROR(INDEX(Items!$E:$E,MATCH(" & cell.Address & ",Items!$A:$A,0)),0)"
                cell.Offset(0, 5).Value = "=IFERROR(INDEX(Items!$C:$C,MATCH(" & cell.Address & ",Items!$A:$A,0)),0)"
            End If
        Next cell
        Application.EnableEvents = True
    End If
End Sub

 

[Alex Blakenburg]

Thanks for letting me know. Glad I could help.

 

[Mohamedazees]

Thanks for letting me know. Glad I could help.

Good after noon Sir,
The above code was working perfectly , however when i Search the case in Dropdown of Cell "F10" then the code will interrupted in line No. 2
That is " If Not Intersect(Range("G20:R29"), Target) Is Nothing Then"
Please help me to resolve this issue.

 

[Alex Blakenburg]

I was surprised you didn't come back to me on -

"Do you really want to test for a change in G-R ? I would have thought you would be testing for a change in F."

It sounds like what you really want is:

If Not Intersect(Range("F20"), Target) Is Nothing Then

or possibly

If Not Intersect(Range("F20:F29"), Target) Is Nothing Then

Whatever cell or range of cells you want to trigger the code should go in there.