View Single Post
  #1   Report Post  
Posted to microsoft.public.excel.programming
Ian[_12_] Ian[_12_] is offline
external usenet poster
 
Posts: 13
Default Vlookup Macro to return Values only

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