I like to let excel build my addresses for me. Then I don't have to worry about
the syntax (when do I need apostrophes, where does the ! go?).

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim myLookupRng As Range

With Target

If .Cells.Count 1 Then Exit Sub
If Application.Intersect(.Cells, Range("F:F")) Is Nothing Then
Exit Sub
End If

Set myLookupRng = Me.Parent.Worksheets("Batch Links").Range("A:H")

.Offset(0, 1).Formula _
= "=IF(OR(ISBLANK(" & .Address & "),isna(" & .Address _
& ")),"""",VLOOKUP(" & .Address & "," _
& myLookupRng.Address(external:=True) & ",8,FALSE))"

End With

End Sub


tommylux wrote:

Jim, my actual code:

Private Sub Worksheet_Change(ByVal Target As Range)

If Application.Intersect(Target, Range("F:F")) Is Nothing Then
Exit Sub

Else
Target.Offset(0, 1).Formula = "IF(OR(ISBLANK(" & Target & ")," &
Target & "'NA'),"",VLOOKUP(" & Target & ",'Batch Links'!A:H,8,FALSE))"
End If

End Sub

1. How do you put the Cell Address in the code above? Target simply
puts the value of the cell.
2. Doesnt matter what I do, the code only puts in the following text:
IF(OR(ISBLANK(23),23'NA'),",VLOOKUP(23,'Batch Links'!A:H,8,FALSE))
without the "=" to make it a formula.

Jim May wrote:
This Sheet Code worked for me..
Note that my example named the LookupRange MyRange, and
I used 3 as my "bring-back" column << You should change
Both these to suit (MyRange & 3)
HTH
Jim May

Private Sub Worksheet_Change(ByVal Target As Range)
If Application.Intersect(Target, Range("B:B")) Is Nothing Then Exit
Sub
If Not Application.Intersect(Target, Range("B:B")) Is Nothing Then
Target.Offset(0, 1).Value = "=Vlookup(" & Target &
",MyRange,3,False)"
End If
End Sub

--

Dave Peterson