Hi Rob
Hope you don't mind me posting this again but last week I posted a message asking for
help to convert a Vlookup Formula to a Macro and you kindly replied with your Macro.
I copied and pasted your Macro into mine but it didn't work, even after editing it slightly.
I think the problem was I didn't make it clear what I wanted the Macro to do, so I have
tried to explain it a bit clearer, and hope you can come up with the winning Macro.

We use Excel 2000 to book work in and out, in the "BOOK IN" Worksheet we
enter the Part Numbers for each job, down in Column A, (one in each Row), I then
have the following Formula pasted into each Cell down in Column C that looks up the
Part Number that was entered into the Cell in Column A, and it returns the correct
Part Description in the Cell in Column C on the same Row.

=IF($A2="","",VLOOKUP($A2,PARTNUMBERS!$A$2:$B$1000 ,2,FALSE))

The list of Part Numbers and Part Descriptions are in the Worksheet named "PARTNUMBERS"
where all the Part Numbers are down Column A, and the corresponding Part Descriptions
are down Column B.

This Formula works well but I would really like to convert it to a Macro so that the Formulas
don't get accidently deleted as there are a few people who will be using the Spreadsheet.
I have tried to protect Column C to stop these Formulas from being accidently deleted, but then
it also stops us from cutting the Cells from the "BOOK IN" Worksheet and pasting them to the
"BOOK OUT" Worksheet when the Parts has been repaired and are being booked out.

Can you help.

TIA
Ian

Ian,
Try
Sub CopyFormula()
With Range("C2")
.Formula = "=IF($A2="""","""",VLOOKUP($A2," & _
"PARTNUMBERS!$A$2:$B$1000,2,FALSE))"
.AutoFill Range("C2:C1000")
End With
End Sub
the
" & _
"
part is not necessary, you can have the formula in one line.
I brake it in to 2 lines to avoid the line warp in the message.
Cecil
"Ian" wrote in message
...
Hi Rob
Hope you don't mind me posting this again but last week I posted a message
asking for
help to convert a Vlookup Formula to a Macro and you kindly replied with
your Macro.
I copied and pasted your Macro into mine but it didn't work, even after
editing it slightly.
I think the problem was I didn't make it clear what I wanted the Macro to
do, so I have
tried to explain it a bit clearer, and hope you can come up with the
winning Macro.

We use Excel 2000 to book work in and out, in the "BOOK IN" Worksheet we
enter the Part Numbers for each job, down in Column A, (one in each Row),
I then
have the following Formula pasted into each Cell down in Column C that
looks up the
Part Number that was entered into the Cell in Column A, and it returns the
correct
Part Description in the Cell in Column C on the same Row.

=IF($A2="","",VLOOKUP($A2,PARTNUMBERS!$A$2:$B$1000 ,2,FALSE))

The list of Part Numbers and Part Descriptions are in the Worksheet named
"PARTNUMBERS"
where all the Part Numbers are down Column A, and the corresponding Part
Descriptions
are down Column B.

This Formula works well but I would really like to convert it to a Macro
so that the Formulas
don't get accidently deleted as there are a few people who will be using
the Spreadsheet.
I have tried to protect Column C to stop these Formulas from being
accidently deleted, but then
it also stops us from cutting the Cells from the "BOOK IN" Worksheet and
pasting them to the
"BOOK OUT" Worksheet when the Parts has been repaired and are being booked
out.

Can you help.

TIA
Ian

all nice.. but :)

when you have 1000 similar formulas..use a NAMED formula.

r1c1 must be used to add a name with relative references.
(which is alse the reason why i use a worksheet name rather then a
workbook name.)

makes your book smaller and imo easier to maintain
users see (and can repair) a simple name rather than the formula itself.

Sub Demo()
Sheets(1).Activate
ActiveSheet.Names.Add _
Name:="FindPart", _
RefersToR1C1:= _
"=IF(rc1="""","""",VLOOKUP(rc1,PARTNUMBERS!r2c1:r1 000c2,2,FALSE))"

[b2:b1000].Formula = "=FindPart"

End Sub



keepITcool

< email : keepitcool chello nl (with @ and .)
< homepage: http://members.chello.nl/keepitcool


"Cecilkumara Fernando" <cekufdo@sltnetDOTlk wrote:

Ian,
Try
Sub CopyFormula()
With Range("C2")
.Formula = "=IF($A2="""","""",VLOOKUP($A2," & _
"PARTNUMBERS!$A$2:$B$1000,2,FALSE))"
.AutoFill Range("C2:C1000")
End With
End Sub
the
" & _
"
part is not necessary, you can have the formula in one line.
I brake it in to 2 lines to avoid the line warp in the message.
Cecil
"Ian" wrote in message
...

Excellent suggestion from keepITcool here. I like seeing named formulas
used.

Not sure about R1C1 referencing... This works OK for me?
Names.Add "Sheet1!FindPart", _

"=IF(Sheet1!$A1="""","""",VLOOKUP(Sheet1!$A1,PARTN UMBERS!$A$2:$B$1000,2,FALS
E))"

To OP:
Additionally, if you ever hit the 1000 item limit, you're going go through a
painful formula expansion exercise.
There are good articles on Dymanic Ranges which will help here - it allows
the number of rows to become automatically determined.
Apparently you get a performance increase using Dynamic Ranges too.


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


"keepITcool" wrote in message
...
all nice.. but :)

when you have 1000 similar formulas..use a NAMED formula.

r1c1 must be used to add a name with relative references.
(which is alse the reason why i use a worksheet name rather then a
workbook name.)

makes your book smaller and imo easier to maintain
users see (and can repair) a simple name rather than the formula itself.

Sub Demo()
Sheets(1).Activate
ActiveSheet.Names.Add _
Name:="FindPart", _
RefersToR1C1:= _
"=IF(rc1="""","""",VLOOKUP(rc1,PARTNUMBERS!r2c1:r1 000c2,2,FALSE))"

[b2:b1000].Formula = "=FindPart"

End Sub



keepITcool

< email : keepitcool chello nl (with @ and .)
< homepage: http://members.chello.nl/keepitcool


"Cecilkumara Fernando" <cekufdo@sltnetDOTlk wrote:

Ian,
Try
Sub CopyFormula()
With Range("C2")
.Formula = "=IF($A2="""","""",VLOOKUP($A2," & _
"PARTNUMBERS!$A$2:$B$1000,2,FALSE))"
.AutoFill Range("C2:C1000")
End With
End Sub
the
" & _
"
part is not necessary, you can have the formula in one line.
I brake it in to 2 lines to avoid the line warp in the message.
Cecil
"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

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

That's no problem.

Yes, I dont do a very good job for word wrapping. That line you had trouble
with should have been one long line, but line continuations are OK too.

One of these days I'll write me a program to automatically wrap code
properly.

Cheers

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


"Ian" wrote in message
...
Hi Rob
THANK YOU, THANK YOU, THANK YOU.

After pasting your formula in, I got a "compile error/syntax error", and
the following
line was highlighted in blue, I guess it was showing me what line had the
error.

.Formula = "=IF($A" & Target.Row & "="""","""",VLOOKUP($A" &

Anyway, I added a " _" to the end of the line so it looks like this

.Formula = "=IF($A" & Target.Row & "="""","""",VLOOKUP($A" &
_

and it now works, brilliant.

Thanks for all your help, and thanks to Cecil and keepITcool.

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

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

Hi Rob
I forgot to say that these Delivery Note xls files will probably also get sorted into Folders,
so the Formula (References) that were pasted into them to look up the Part Descriptions
will also alter.

Cheers
Ian

"Ian" wrote in message ...
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

Hi Rob
I just moved a "Delivery Note" workbook into a Folder and when I opened it
I got the following message:
-----
The workbook you opened contains automatic links to information in another workbook.
Do you want to update this workbook with changes made to the other workbook.

To update all linked information, click Yes
To keep the existing information, click No
-----

Obviously the other workbook ("Book In") is constantly being updated, so I guess
this message will always come up if we ever have to open a "Delivery Note" workbook
in the future for tracking purposes, as it contains links to the other workbook.
So if we could cut Rows from the "Book In" workbook, and paste it in the "Delivery Note"
workbook as Text, rather than the actual Formula, I think it would better.
What do you think.

Cheers
Ian


"Ian" wrote in message ...
Hi Rob
I forgot to say that these Delivery Note xls files will probably also get sorted into Folders,
so the Formula (References) that were pasted into them to look up the Part Descriptions
will also alter.

Cheers
Ian

Hi again
I just spent my lunchbreak searching Google and what I should have said is that I want to
add a Formula to the Macro which will allow me to copy just the "Values" of the cells
(not the Formulas), from one workbook to another.
I know you can do it if you copy cells from one workbook and then in the other workbook
you right click and select "paste special" and select "values", but can you do this automatically
by Macros.

Cheers again
Ian


"Ian" wrote in message ...
Hi Rob
I just moved a "Delivery Note" workbook into a Folder and when I opened it
I got the following message:
-----
The workbook you opened contains automatic links to information in another workbook.
Do you want to update this workbook with changes made to the other workbook.

To update all linked information, click Yes
To keep the existing information, click No
-----

Obviously the other workbook ("Book In") is constantly being updated, so I guess
this message will always come up if we ever have to open a "Delivery Note" workbook
in the future for tracking purposes, as it contains links to the other workbook.
So if we could cut Rows from the "Book In" workbook, and paste it in the "Delivery Note"
workbook as Text, rather than the actual Formula, I think it would better.
What do you think.

Cheers
Ian


"Ian" wrote in message ...
Hi Rob
I forgot to say that these Delivery Note xls files will probably also get sorted into Folders,
so the Formula (References) that were pasted into them to look up the Part Descriptions
will also alter.

Cheers
Ian

From the Edit menu, Instead of Paste, choose Paste Special | Values


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


"Ian" wrote in message
...
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

Yes, it's possible.

Create a macro containing:
Activecell.PasteSpecial xlPasteValues


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


"Ian" wrote in message
...
Hi again
I just spent my lunchbreak searching Google and what I should have said is
that I want to
add a Formula to the Macro which will allow me to copy just the "Values"
of the cells
(not the Formulas), from one workbook to another.
I know you can do it if you copy cells from one workbook and then in the
other workbook
you right click and select "paste special" and select "values", but can
you do this automatically
by Macros.

Cheers again
Ian


"Ian" wrote in message
...
Hi Rob
I just moved a "Delivery Note" workbook into a Folder and when I opened
it
I got the following message:
-----
The workbook you opened contains automatic links to information in
another workbook.
Do you want to update this workbook with changes made to the other
workbook.

To update all linked information, click Yes
To keep the existing information, click No
-----

Obviously the other workbook ("Book In") is constantly being updated, so
I guess
this message will always come up if we ever have to open a "Delivery
Note" workbook
in the future for tracking purposes, as it contains links to the other
workbook.
So if we could cut Rows from the "Book In" workbook, and paste it in the
"Delivery Note"
workbook as Text, rather than the actual Formula, I think it would
better.
What do you think.

Cheers
Ian


"Ian" wrote in message
...
Hi Rob
I forgot to say that these Delivery Note xls files will probably also
get sorted into Folders,
so the Formula (References) that were pasted into them to look up the
Part Descriptions
will also alter.

Cheers
Ian