This line adds the formula:

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

The OP can change it so that it plops in the formula and then converts it to a
value:

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

Dallman Ross wrote:

In , Dave Peterson
spake thusly:

tommylux wrote:
How do I add to this code so that only the value of the formula
is entered into the cell automatically, rather than the formula
itself?

For me (and probably lots of others), the older posts have aged
off and the context of your follow up is lost.

Here you go:

In .com,
tommylux spake thusly:

Thanks dave,

Just what I was looking for, I have changed it a little you may have
confused "NA" for isna which is what the user would type in, forgive
the formatting:

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

If Application.Intersect(.Cells, Range("1:1")) Is Nothing Then

If Target.Formula = "" Then
.Offset(0, 1).Formula = ""
Else

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

.Offset(0, 1).Formula _
= "=IF(OR(ISBLANK(" & .Address & ")," & .Address &
"=""NA""),"""",VLOOKUP(" & .Address & "," &
myLookupRng.Address(external:=True) & ",8,FALSE))"
End If
End If
End With
End Sub


--

Dave Peterson