Hi Rob
As I have already mentioned, your Macro is now working perfectly, thank you, but I am
just curious with one thing.

Say I enter a Part Number into Column A, the Part Description is then returned in Column C,
the Date is returned into Column F, and the Due Date is returned into Column G.
Now when that Part has been repaired and sent back to the customer, we cut and paste
the Row of that Part from the "Book In" worksheet, to separate "Delivery Note" xls files.
Each Delivery Note gets saved as a separate xls file with the Company Name and Date,
i.e., "CompanyName060704.xls"

Everything is working fine, the "Part Number", "Part Description", "Date In" and "Due Date"
all paste into the Delivery Note xls files, but I have noticed that when it pastes the "Part Description",
it actually pastes the Formula, (which then refers back to the PARTNUMBERS worksheet),
rather than pasting it as Text, (if you see what I mean).
The "Date In" and "Date Due" are pasted as text.
Is there a way to paste the "Part Description" as Text, (i.e., the text that was looked up, rather
than pasting the actual Formula.
This may be good if we ever need to change the file name of the xls file that contains the worksheet
"PARTNUMBERS", as the Formula pasted into the Delivery Note spreadsheet won't be able to
find the file it is referencing, as that file will now have a different filename.

Cheers
Ian


"Rob van Gelder" wrote in message
...
Here's one way:

I've changed your layout a bit. Not that your approach doesn't work, just
demonstrating a different way to do it.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count = 1 And Target.Column = 1 Then
With Range("F" & Target.Row)
.Value = Date
.NumberFormat = "dd mmm yy"
End With

With Range("G" & Target.Row)
.Value = Date + 10
.NumberFormat = "dd mmm yy"
End With

With Range("C" & Target.Row)
.Formula = "=IF($A" & Target.Row & "="""","""",VLOOKUP($A" &
Target.Row & "," & "PARTNUMBERS!$A$2:$B$1000,2,FALSE))"
End With
End If
End Sub


This newsgroup is for all levels of expertise for excel programming from
beginners to experts.
I know a fair bit about Excel Programming, but I still learn something new
every day - like that you can use a column letter in the column parameter of
Cells: Cells(Target.Row, "F") (I never knew that!)

Cheers


--
Rob van Gelder - http://www.vangelder.co.nz/excel


"Ian" wrote in message
...
Thanks for all your input, unfortunately I can't get any of the Macros to work.
I only started getting into Macros last week as I have always used Formulas.
I suspect I'm not pasting the Macro into the right place, so would be grateful
if you could help again.

I have the following Macro that returns the Date that each Part was Booked In
down in Column F, and it also returns the Due Date down in Column G,
(which is 10 days later than the date the Part was Booked In), as we work on a
10 day turn around.
So for example, when I enter a Part Number in one Row in Column A, it returns
"04 Jul 04" in Column F, and it returns "14 Jul 04" in Column G, in the same Row
for that Part Number.

So the Macro I have is:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Target.Count = 1 Then
If Target.Column = 1 Then
Cells(Target.Row, "F") = Date
Cells(Target.Row, "F").NumberFormat = "dd mmm yy"
End If
End If

If Target.Count = 1 Then
If Target.Column = 1 Then
Cells(Target.Row, "G") = Date + 10
Cells(Target.Row, "G").NumberFormat = "dd mmm yy"
End If
End If
End Sub

This Macro may not be perfect but it works well.
So where do I paste your Macros, sorry but I'm new to Macros.

Cecil, I'm not sure why you are using:
With Range("C2")
.AutoFill Range("C2:C1000")

I think this newsgroup is a bit too advanced for me, maybe I should be
in the newsgroup "microsoft.public.excel.newusers".

Thanks once again
Ian