Worksheet Event Code for Vlookup Formula

[adam]

Hi anyone,

I’m using the following vLook up code so that when I type a code such as 0001 in column E of the sheet “NewOrder”, the Item Description from the products sheet for that code gets copied to the column G on to the same row.

=IF(ISNA(VLOOKUP(E15,Products!$D$10:$GC$128,2,FALSE)),"",VLOOKUP(E15,Products!$D$10:$G$128,2,FALSE))

How Could I write a worksheet event code so that the item description gets copied to the column G each time I write a code number in the column E of the sheet "NewOrder" instead of having to write formula on each row?

Any help on this would be kindly appreciated.

Thanks in advance.

[HansV]

Code: Select all

Private Sub Worksheet_Change(ByVal Target As Range)
  Dim oCell As Range
  Dim varDescription As Variant
  If Not Intersect(Range("E:E"), Target) Is Nothing Then
    For Each oCell In Intersect(Range("E:E"), Target)
      varDescription = Application.VLookup(oCell.Value, _
        Worksheets("Products").Range("D10:E128"), 2, False)
      If IsError(varDescription) Then
        oCell.Offset(0, 2).ClearContents
      Else
        oCell.Offset(0, 2).Value = varDescription
      End If
    Next oCell
  End If
End Sub

[adam]

Thanks for the help. With this code how could the contents in the column G of the products sheet be copied to the NewOrder sheet's column M as the user writes the number in column E?

[HansV]

You should be able to figure that out yourself now.

[adam]

Thanks for the recommendation. I've figured that out.

[adam]

The following code generates auto numbers in column D when any cell is clicked. How could I make this code to generate the auto numbers in column D when the user writes a numerical number in column E instead of clicking.

Lets say for example if the user writes 0001 in row 10 that row is to be row one.

Code: Select all

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
    Dim RowOffset As Long
    Dim IndexCol As String
     'Set values
    RowOffset = 0
     'Change the D to the column where you want the numbers to show
    IndexCol = "D"
     
    Intersect(ActiveCell.EntireRow, Columns(IndexCol)).Value = ActiveCell.Row + RowOffset
End Sub

Note: this code is to be implemented with the previous code in this thread.