Its been a while....Help with inserting formula when cell changes

[melewie]

Hi All, been a while since I've posted here managed to avoid excel and all forms of IT for the past 5 years (it's been blissful ) however here I am at a desk staring at many many spreadsheets.

I am trying to create a downtime log of sorts, what I am trying to achieve is when the user inputs the Item number in column C, I would then like to run a vlookup in column L against another worksheet in the workbook. So far I have this

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


    Set KeyCells = Range("C1:C2000")
    
    If Not Application.Intersect(KeyCells, Range(Target.Address)) _
           Is Nothing Then
'put this bloody formula in cell L whatever the row number was changed "=VLOOKUP(RC[-9],'Production Standards'!C[-11]:C[-9],3,FALSE)"
        MsgBox "it seems to work!!!"
       
    End If
End Sub

Which seems to work identifying when the value in a cell in column C changes.. however I can't figure out how to then tell excel to run a vlookup in column L... it's amazing how much you can forget

Any help is hugely appreciated

Lewie

 

[steve the fish]

You could use this:

Target.Offset(0, 9).FormulaR1C1 = "=VLOOKUP(RC[-9],'Production Standards'!C[-11]:C[-9],3,FALSE)"

 

[melewie]

You could use this:

Target.Offset(0, 9).FormulaR1C1 = "=VLOOKUP(RC[-9],'Production Standards'!C[-11]:C[-9],3,FALSE)"

Cheers Steve...the fish, exactly what I was trying to do.

much gratitude to your fishy self!!!