Copy formula automatically down when date is entered
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
|