Automatically Update a cell value VBA

[Skybluekid]

Hi All,

In Column D of a sheet I enter a Charge Reference, for example CL12346. This relates to an Authority which I put in Column I. What I would like to do is everytime i enter a new reference it will put the appropriate Authority in Column I. I have worked out the code for the Lookup,

Sub LookupV()
Dim LU As String
Dim Auth As Variant


On Error Resume Next


LU = Left(Sheets("Data Sheet").Range("D3").Value, 2)
Auth = WorksheetFunction.VLookup(LU, [AuthorityTable], 2, 0)


Sheets("Data Sheet").Range("I3") = Auth


End Sub

I am not sure how to embed this so that it would work everytime I enter a new line.

Thanks in advance

 

[mumps]

Can you post a screen shot of what your data looks like? Section B at this link has instructions on how to post a screen shot: https://www.mrexcel.com/forum/board-announcements/127080-guidelines-forum-use.html Alternately, you could upload a copy of your file to a free site such as www.box.com. or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. Include a detailed explanation of what you would like to do referring to specific cells, rows, columns and worksheets. If the workbook contains confidential information, you could replace it with generic data.

 

[Fluff]

How about

Private Sub Worksheet_Change(ByVal Target As Range)
   Dim LU As String
   Dim Auth As Variant
   
   If Not Intersect(Target, Range("D:D")) Is Nothing Then
      LU = Left(Target.Value, 2)
      Auth = Application.vlookup(LU, [AuthorityTable], 2, 0)
      If IsError(Auth) Then Auth = "Not Found"
      Target.Offset(, 5).Value = Auth
   End If
End Sub

This needs to go in the "Data Sheet" sheet module

 

[Skybluekid]

Thank you Fluff it works a treat

 

[Fluff]

You're welcome & thanks for the feedback