Paste special values but not...

A

aprillachlan

Really hoping someone can help me on this one, as I have spent hours
trying to figure out and there must be a simple way to do it.

I have an invoice template, where the sales person enters the product
code into one cell and then the description then appears in the
adjacent cell using the following formula:

=VLOOKUP(C27,'Y:\Excel\[Product
Codes.xls]Sheet1'!$A$2:$B$26597,2,FALSE)

I want the sales person to be able to change the description if
necessary i.e. add the colour of the product etc.

But of course in the description cell is the formula and not the actual
text I have asked it to return.

I know you can do this with paste special then values, but I dont want
the sales people to have to paste special all the time. I want it to do
it automatically.

I have never used VBA and to be honest cant face trying to learn it at
this point. Is there a simple way of doing this?

Many thanks in advance to anyone who is able to help.

April
 
O

Otto Moehrbach

April
The "automatically" requirement forces VBA as your only solution. You
could put a button on the template sheet and label the button whatever you
like, maybe "Modify Description". The sales people would have to be
instructed to click on that button if they wish to modify the description.
Clicking on that button will trigger a macro to execute. That macro would
do the PasteSpecial - Values thing for them.
If this is of interest to you, let me go a little further. The code
will need to know what cell to operate on. The sales people would have to
be instructed to select a cell, any cell, in the row that holds the
description they want to modify, before they clicked the button. The code
would then act on the cell, in that row, that is in the description column.
I'll assume that's Column D. The macro would look like this:
Sub ChgToValue()
Cells(ActiveCell.Row, 4).Copy
Cells(ActiveCell.Row, 4).PasteSpecial xlPasteValues
Application.CutCopyMode = False
End Sub
Please post back if this is suitable for you and you need help with it.
Otto
"aprillachlan" <[email protected]>
wrote in message
news:[email protected]...
 
A

aprillachlan

Otto,

Thanks ever so much for responding. I did what you suggested but in a
roundabout way and it seemed to work.

As I have never tried just typing in code into macors, I used the
"record macros", which I never knew existed.

I recorded copying the cell, then pasting special values only. I just
gave it a short cut button of CTRL+Q.

It seems to have worked and I didnt have to put in any buttons. Thanks
for putting me in the right direction. I never knew that macros could
be so easy!

Thanks again,

April
 
Top