run formula based on cell value to the last row

[MMM_84]

Hi folks,
Would you help me to write a vba, so it can run the formula in column Y depending on the dynamic column X cells in each row (from X5 to X last row)?
Meaning, if X5 smth run in Y5 the formula, then if X6 is smth run formula in Y6 and so on.
I'm trying the following, but it works only if cell X5 changes, however I want to run to each row.
Hope I'm clear enough

Sub MyMacro()

    Dim lr As Long
    Dim r As Long
  
    Application.ScreenUpdating = False
  
'   Find last row in column I with data
    lr = Cells(Rows.Count, "X").End(xlUp).Row
  
'   Loop through all rows
    For r = 2 To lr
'       Check to see if column X is not blank
        If Cells(r, "X") <> "" Then
'           Populate column Y with formula
            Cells(r, "Y").Formula = "=INDEX(Sheet8!$G$2:$G$22,MATCH(1,(X5>=Sheet8!$E$2:$E$22)*(X5<=Sheet8!$F$2:$F$22),0))"
        End If
    Next r
  
    Application.ScreenUpdating = True
  
End Sub

Thanks!

 

[SunnyAlv]

Try with autofill to avoid loop

Sub Macro1()

Dim l As Long

l = Range("X" & Rows.Count).End(xlUp).Row
    With Range("Y2")
        .Select
        .Formula2 = "=INDEX(Sheet8!$G$2:$G$22,MATCH(1,(X5>=Sheet8!$E$2:$E$22)*(X5<=Sheet8!$F$2:$F$22),0))"
        .AutoFill Destination:=Range("Y2:Y" & l)
    End With
End Sub

for automatic formula in column Y try this

1. right click on ur sheet
2. click "View Code", then paste this code

Private Sub worksheet_change(ByVal target As Range)
On Error Resume Next

    If Not Intersect(Range("X:X"), target) Is Nothing Then
Application.EnableEvents = False
        If Not target.Value = "" Then
            target.Offset(, 1).Formula2 = "=INDEX(Sheet8!$G$2:$G$22,MATCH(1,(X5>=Sheet8!$E$2:$E$22)*(X5<=Sheet8!$F$2:$F$22),0))"
                Else
            target.Offset(, 1).Value = ""
        End If
    End If
Application.EnableEvents = True
End Sub

 

[SunnyAlv]

Private Sub worksheet_change(ByVal target As Range)
On Error Resume Next

    If Not Intersect(Range("X:X"), target) Is Nothing Then
Application.EnableEvents = False
        If Not target.Value = "" Then
            target.Offset(, 1).Formula2 = "=INDEX(Sheet8!$G$2:$G$22,MATCH(1,(X5>=Sheet8!$E$2:$E$22)*(X5<=Sheet8!$F$2:$F$22),0))"
                Else
            target.Offset(, 1).Value = ""
        End If
    End If
Application.EnableEvents = True
End Sub

Edited :

Private Sub worksheet_change(ByVal target As Range)
On Error Resume Next

    If Not Intersect(Range("X:X"), target) Is Nothing Then
Application.EnableEvents = False
        If Not target.Value = "" Then
            target.Offset(, 1).Formula2 = "=INDEX(Sheet8!$G$2:$G$22,MATCH(1,(X" & target.Row & ">=Sheet8!$E$2:$E$22)*(X" & target.Row & "<=Sheet8!$F$2:$F$22),0))"
                Else
            target.Offset(, 1).Value = ""
        End If
    End If
Application.EnableEvents = True
End Sub

 

[Peter_SSs]

@MMM_84
When posting vba code in the forum, please use the available code tags. It makes your code much easier to read/debug & copy. My signature block below has more details. I have added the tags for you this time.

 

[MMM_84]

Edited :

Private Sub worksheet_change(ByVal target As Range)
On Error Resume Next

    If Not Intersect(Range("X:X"), target) Is Nothing Then
Application.EnableEvents = False
        If Not target.Value = "" Then
            target.Offset(, 1).Formula2 = "=INDEX(Sheet8!$G$2:$G$22,MATCH(1,(X" & target.Row & ">=Sheet8!$E$2:$E$22)*(X" & target.Row & "<=Sheet8!$F$2:$F$22),0))"
                Else
            target.Offset(, 1).Value = ""
        End If
    End If
Application.EnableEvents = True
End Sub

Thanks, but smth is missing...it didn't work....cannot figure what's missing....

 

[MMM_84]

Thanks, but smth is missing...it didn't work....cannot figure what's missing....

my bad, all works! thanks!!!!