Sub Addformula()
Dim rng As Range, rng1 As Range
Dim sForm As String, sForm1 As String
Set rng = Worksheets("Book Inventory"). _
Range("A9").CurrentRegion
With Worksheets("Invoice")
If IsEmpty(.Range("A14")) Then
Exit Sub
Else
Set rng1 = .Range(.Range("A14"), _
.Cells(Rows.Count, 1).End(xlUp))
sForm = "=If(A14<"""",Vlookup(A14," & _
rng.Address(1, 1, xlA1, True) & _
",2,False))"
sForm1 = "=If(A14<"""",Vlookup(A14," & _
rng.Address(1, 1, xlA1, True) & _
",5,False))"

rng1.Offset(0, 1).Formula = sForm
rng1.Offset(0, 2).Formula = sForm1
rng1.Offset(0, 4).Formula = "=C14*D14"
End If
End With
End Sub

--
Regards,
Tom Ogilvy




"sjbeeny " wrote in message
...
Thanks for the suggestion John, but I dont know how many cells in col A
will be used and if I fill the formulae down then I get a whole lot of
zeros in the fields that there is no corresponding vlookup value for.

also as I have made the unused cells of the sheet grey I need to adjust
the formatting for the cells that will have values. so I need to find
some code that says somthing like

For the values in Range "ItenN" then put vlookup formulae in
corresponding Range "name", where vlookup value is the value of the
line it is on.

I have attached the document to try and help my stateing of the
problem. I am having trouble with the 3rd sheet that I have labelled
invoice. If you look at the second sheet and add a couple of bboks to
the order then press "order entry" and look at the 3rd sheet you will
see what my difficulty is.

I am trying to automate it so that when the "order entry" button is
pushed all the details come up in the Invoice section.

Cheers,
Simon

Attachment filename: book catalogue.xls
Download attachment:
http://www.excelforum.com/attachment.php?postid=561603
---
Message posted from http://www.ExcelForum.com/