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