Enter formula into cell when another cell in that row changed

[Jumparound]

Hi,

I have a sheet where cells L12 to L89 have formulas in that looks at values entered in I12 to I89, if there is only one match it returns that, if there are multiple matches it returns nothing.

e.g.

=IF(I12="","",IF(VLOOKUP(Q12,Sheet1!$A$39:$C$2375,3,0)="",VLOOKUP(Q12,Sheet1!$A$39:$C$2375,2,0),""))

I then have a data validation on the cells in L12 to L89 which looks up the possible matches and lists them in a drop down which the user can select from.

The issue is that when the user selects one of these values from the drop down it overwrites the cell contents and so the formula in the cell is lost so if the user changes the value in column I it will no longer auto populate if there is only one result.

I tried to fix this with some VBA which would paste the formulas back in when one of the cells from I12 to I89 was changed. However, this replaces all of the values in cells L12 to L89 so would overwrite anything the user had already done

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

' The variable KeyCells contains the cells that will
    ' insert the formulas when they are changed.
    Set KeyCells = Range("I12:I89")

If Not Application.Intersect(KeyCells, Range(Target.Address)) _
           Is Nothing Then

' Insert the formulas when one of the designated cells has been
        ' changed.
        Range("L12:L89").Formula = "=IF(I12="""","""",IF(VLOOKUP(Q12,Sheet1!$A$39:$C$2375,3,0)="""",VLOOKUP(Q12,Sheet1!$A$39:$C$2375,2,0),""""))"

End If
End Sub

I think the solution to this is to change the vba to only update the cell in column L that is in the same row as the cell that has been changed in column I. If anyone could help it would be greatly appreciated!

 

[Fluff]

How about

Private Sub Worksheet_Change(ByVal Target As Range)
   If Target.CountLarge > 1 Then Exit Sub
   If Not Intersect(Target, Range("I12:I89")) Then
      Target.Offset(, 3).Formula = "=IF(I12="""","""",IF(VLOOKUP(Q12,Sheet1!$A$39:$C$2375,3,0)="""",VLOOKUP(Q12,Sheet1!$A$39:$C$2375,2,0),""""))"
   End If
End Sub

This assumes that only one cell in col I will be changed at a time.

 

[Jumparound]

Hi Fluff, thanks although I get Run-time error 91 object variable or with block variable not set and it highlights this line

If Not Intersect(Target, Range("I12:I89")) Then

I could be doing something wrong? In answer to your assumption though, yes only one cell will be changed at a time

 

[Fluff]

Oops missed a bit from that line, it should be

Private Sub Worksheet_Change(ByVal Target As Range)
   If Target.CountLarge > 1 Then Exit Sub
   If Not Intersect(Target, Range("I12:I89")) Is Nothing Then
      Target.Offset(, 3).Formula = "=IF(I12="""","""",IF(VLOOKUP(Q12,Sheet1!$A$39:$C$2375,3,0)="""",VLOOKUP(Q12,Sheet1!$A$39:$C$2375,2,0),""""))"
   End If
End Sub

 

[Jumparound]

Hi,

That fixed the error. Although this is returning the same formula in each line which may be my fault for not explaining properly in the first place so I apologize if that is the case.

What I need is for the formula that is returned in the row to be updated to that row. So currently whichever cell in I is changed the formula references are for I12 and Q12. I want these to update so that if for example I change cell I14 the formula in L14 reads

=IF(I14="","",IF(VLOOKUP(Q14,Sheet1!$A$39:$C$2375,3,0)="",VLOOKUP(Q14,Sheet1!$A$39:$C$2375,2,0),""))

 

[Fluff]

may be my fault for not explaining properly in the first place

Nope, it's my fault for not putting my brain in gear.
Try

Private Sub Worksheet_Change(ByVal Target As Range)
   If Target.CountLarge > 1 Then Exit Sub
   If Not Intersect(Target, Range("I12:I89")) Is Nothing Then
      Target.Offset(, 3).FormulaR1C1 = "=IF(rc[-3]="""","""",IF(VLOOKUP(rc17,Sheet1!r39c1:r2375c3,3,0)="""",VLOOKUP(rc17,Sheet1!r39c1:r2375c3,2,0),""""))"
   End If
End Sub

 

[Jumparound]

Absolutely brilliant thank you so much!

 

[Fluff]

You're welcome & thanks for the feedback.