Copy formula automatically down when date is entered

T

tommylux

I would like to apply a formula to the entire column, but this
increases the size of the spreadsheet and also is a bit messy when
printing.

When I enter data into B5 for example, I would like C5 to automatically
enter the vlookup formula I have written.

Any Visual basic commands I could try?

Thanks again guys for your help
 
R

Roger Govier

Hi
is a bit messy when printing.
Click to expand...
Presumably, you mean because the sheet shows a series of errors where
there are no entries in column B.

Amend your formula in C5 to
=IF(B5="","", your_formula )
and copy down as far as required.

Nothing with show in column C until entries are made in the
corresponding cell of column B
 
K

kassie

Click on Tools|Options, select the Edit tab, and make sure that Extend List
formats and Formulas is ticked. As you go down, this will automatically
extend your formulas
 
J

Jim May

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
 
R

Roger Govier

Hi Kassie

I think this only works in XL2003 (and presumably 2007) and only if you
have created a List with Data>List.
 
T

tommylux

Kassie, It is already ticked as default, havn't been sucsessful.

I might try the VB out,

Thanks
 
T

tommylux

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.
 
J

Jim May

Quick answer, headed for bed.. (right now), but
Substitute..
..Formula = "IF...
With
..Value = "=IF...

HTH
 
D

Dave Peterson

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
 
T

tommylux

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
 
T

tommylux

Another quick question guys, if possible,

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?

Hope for your quick response.

Tom
 
D

Dave Peterson

You may want to include more information.

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

Dave Peterson

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
 
Top