Hi all,

Iwas just wondering if someone could help me with vb code that insert
a 'vlookup' formulae in Col B14 onwards only if there is a numeri
value in Col A14 and onwards

ie. I want to press an active X button and have Col A14 to Ax fille
with data from sheet 2 and then look up the corresponding values fro
sheet 1 using vlookup.


Cheers,
Simon:confused

--
Message posted from http://www.ExcelForum.com

Hi Simon, how about if(isnumber(a14)=true,vlookup
(a14,XXXXX,X),"")?

John
-----Original Message-----
Hi all,

Iwas just wondering if someone could help me with vb code
that inserts
a 'vlookup' formulae in Col B14 onwards only if there is
a numeric
value in Col A14 and onwards

ie. I want to press an active X button and have Col A14
to Ax filled
with data from sheet 2 and then look up the corresponding
values from
sheet 1 using vlookup.


Cheers,
Simon


---
Message posted from http://www.ExcelForum.com/

.

Thanks for the suggestion John, but I dont know how many cells in col
will be used and if I fill the formulae down then I get a whole lot o
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 adjus
the formatting for the cells that will have values. so I need to fin
some code that says somthing like

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

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

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

Cheers,
Simo

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

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/

Thanks Tom you solved it beutifully now im just going to try an
understand what you did and insert the appropriate formating for th
cells in question.

Cheers again,
Simo

--
Message posted from http://www.ExcelForum.com