I have the following Macro which uses VLOOKUP to look up a Part Number
and return the correct Part Description.
It's working really well, but I have noticed that when I enter a Part Number
in say A5, and the correct Part Description is looked up and returned in C5,
but if I click on C5, the actual Formula is displayed in the Formula Bar.
I guess that when a Part Number is entered into Column A, the Macro then
pastes the Formula in Column C, which then looks up the Part Description.

The formula in question is the 3rd one in the Macro
--------------------------------------------------------------------------
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
--------------------------------------------------------------------------
Is it possible for the Macro to not paste the Formula into Column C, but to
just return the VALUE of the VLOOKUP command.

For example, if I enter the Part Number 001 into A5, it returns the Part Description
of Screwdriver in C5, but when I click on C5 the following formula is displayed in the
Formula Bar, (which I guess has been pasted into C5 for it to look up the Description).

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

Is it possible so that the Formula doesn't get pasted, and just the Value is returned
in C5, so that when I click on C5, the word "Screwdriver" appears in the Formula Bar.
This would be great so that when we Cut/Copy/Paste different rows from one workbook
to another, it doesn't paste the links to the original file.
I know you can do this by using "Paste Special" and selecting just the "Values" to paste,
but it does add a few more mouse clicks for every Row we have to Cut/Copy/Paste.
If the Macro just put the VALUE into the cell in the first place, we could use the usual
Cut/Copy/Paste without worrying about the links being pasted.
As I won't be the only person using the spreadsheet, I'm just worried that some people
will just Copy and Paste in their usual way and not use "Paste Special".

Can this Macro be edited to do this?

Thanks again for your help
Ian

Two options.

One option is to leave the macro pretty much as is. After you stuf
the formula in the cell, add this line of code:

Range("C" & Target.Row).Value = Range("C" & Target.Row).Value

that will replace the formula with the result.


A better option is to replace the line that reads
With Range("C" & Target.Row)
.Formula = "=IF($A" & Target.Row & "="""","""",VLOOKUP($A" & _
Target.Row & "," & "PARTNUMBERS!$A$2:$B$1000,2,FALSE))"

with this:
myResult = application.WorksheetFunction.VLookup($A" & _
Target.Row & "," & "PARTNUMBERS!$A$2:$B$1000,2,FALSE)
Range("C" & Target.Row) = myResul

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

Hello
Thank you, the first option (to add the line of code) worked perfectly.
I then tried the second option as you say would be the better option,
but unfortunately it returns a Compile/Syntax error on the 3rd line.

If you could get the code to work, that would be great, (if it is the better
way to do it), but your first option is working, so thanks once again.

Cheers
Ian


"MSP77079 " wrote in message ...
Two options.

One option is to leave the macro pretty much as is. After you stuff
the formula in the cell, add this line of code:

Range("C" & Target.Row).Value = Range("C" & Target.Row).Value

that will replace the formula with the result.


A better option is to replace the line that reads
With Range("C" & Target.Row)
Formula = "=IF($A" & Target.Row & "="""","""",VLOOKUP($A" & _
Target.Row & "," & "PARTNUMBERS!$A$2:$B$1000,2,FALSE))"

with this:
myResult = application.WorksheetFunction.VLookup($A" & _
Target.Row & "," & "PARTNUMBERS!$A$2:$B$1000,2,FALSE)
Range("C" & Target.Row) = myResult


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