Macro clear cell value & also its code

[ipbr21054]

I have a worksheet that i use for invoices.
Once the invoice is printed i then press a button to run a macro which then clears the values that i have just typed into the cells.

The code on the worksheet has just been changed but the macro which clears the cells is still the same.
The problem that i have now is that not only does the macro clear the cells but it also deletes the code etc in that cell.

Below is the macro code that i use to clear the cell values.

Sub INVCUSTOMERINFO()  Application.EnableEvents = False
  '   *** CUSTOMER DETAILS ***
  Range("G13:I18").ClearContents
  
  '   *** JOB DETAILS ***
  Range("N14:O18").ClearContents
  
  Range("G13").Select
  
  Application.EnableEvents = True
End Sub

Below is the code in each cell that is used to return the requested values but is then deleted when the macro has run.

=INDEX(DATABASE!R:R,$H$13)

Can you advise how to edit or replace the existing macro code so it only clears what i have typed as opposed to also clearing its code which i would like left behind so next invoice is then ready to use again.

Thanks

 

[ipbr21054]

Hmmm,
Look at this.

Cell G13 is where the drop down customer list is,next to it in cell H13 is this formula =IF(ISERROR(MATCH(G13,DATABASE!A:A,0)),"",MATCH(G13,DATABASE!A:A,0))

Cell G14 is where the address is so 14 MALLARDS CLOSE but also in the same cell as shown in the address bar at the top is the formula =INDEX(DATABASE!R:R,$H$13)

So both.

Can the formula but written etc so i can then put it in say cell G15 BUT have the returned value put in cell G14
This would then solve my macro clear contents problem ???

 

[Fluff]

A cell CANNOT have both a hard value and a formula end of story full stop.
Also a formula can only affect the cell it is in.

If you ensure that your drop down has a "blank" value, then you can simply select that & your formula will return ""

 

[ipbr21054]

Ok Understand.

So i have a blank value as you advise and ive just selected it & the adress etc has not gone.
It is now replaced with #VALUE ! of which you would respect.

So
1,Can this blank cell be automatically selected when this worksheet is open.
2,How do i go about the #VALUE ! part.

Thanks for the advice took a while to register but now got it

 

[Fluff]

You can wrap your formulae in IFERROR https://exceljet.net/excel-functions/excel-iferror-function

and you can use something like this for the drop down

Range("G13").Value = ""

 

[ipbr21054]

Sorry but i have read that page & your drop down code of G13 but my brain cant process that as to where i need to do it.

I have put the code

=IFERROR(VLOOKUP(value,data,column,0),"Not found")

In cell H14 and i now see the message "Not Found" and i also still see the #VALUE ! in cell G14

I am using version 2007
Sorry but cant work it out

 

[Fluff]

You will need to put the IFERROR around all the formulae

 

[ipbr21054]

Sorry but i am unable to even understand that.
I have this but see an error about to many arguements

=IFERROR(VLOOKUP(DATABASE!R:R,$H$13),"Not found")

 

[Fluff]

Your VLOOKUP formula is wrong.
Dont change the existing formula other than to wrap it in the IFERROR

 

[ipbr21054]

To me this is crptic thus i cant take it onboard

 

[Fluff]

What are your formulae?