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]

=index(database!s:s,$h$13)

 

[ipbr21054]

I then tried this,

=IFERROR(INDEX(DATABASE!R:R,$H$13),”Not Found”)

But that made the #VALUE! not show #NAME?

 

[ipbr21054]

Hi,

Is this now correct as i see not found as opposed the #NAME ?

=IFERROR(INDEX(DATABASE!R:R,$H$13),"”Not Found”")

 

[Fluff]

Not sure if it's just the way the formula was copied to the board, but you have the wrong type of quotes, it should be
=IFERROR(INDEX(DATABASE!R:R,$H$13),"Not Found")

 

[ipbr21054]

Thanks,
I will post this here then need to call it a night & follow on again tomorrow as my brain isnt very good now.

So i have applied this code to the cells in question & changed the row letter as i do each cell.

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

So i start with a clean slate in that i mean the cells are all empty even cell G13 which has the customers names.
I select a customer & the cells now get populated with the relevant information.
The row T on the database sheet is empty so the cell on my INV sheet shows the number 0
I thought the whole point of the code to have "" at the end was to also show this cell as empty not the number 0

The cell H13 which has this code in it,

=IF(ISERROR(MATCH(G13,DATABASE!A:A,0)),"",MATCH(G13,DATABASE!A:A,0))

Now shows the number 7 in it for some reason.

If i then select another customer then this number 7 changes to the number 16
Then another show the number 5

Whats gooing on ?

Selecting the blank in cell G13 drop down clears all the cells info like it should & looks fine.

So i need to work on,
Why do digits appear in cell H13 (7,16,5 etc) when a customer is selected "this cell is empty if i select the blank in the drop down list if that helps"

Why is the number 0 shown in the cell on the INV sheet just because the cell on the DATABASE sheet is empty,i was expecting it also to be empty,
thus having the code IFERROR etc

Thats providing ive done it correct.

have a good night & follow on tomorrow.

many thanks once again

 

[Fluff]

The formula in H13 shows a number because that's what the MATCH function returns.
I've no idea why the cell on the INV sheet returns 0, because I've no idea what formula is in it.

 

[ipbr21054]

Hi,
As a rough fix i just decided to hide column H so now that number is not shown.

With regards the 0 here is some info.
Cell G16 currently shows 0
The formula in cell G16 is

=IFERROR(INDEX(DATABASE!T:T,$H$13),"")

Cell G16 looks at the DATABASE sheet for row T
In this case the cell on row T where the dat is supposed to copy then enter back to cell G16 is empty.
If it had 123 in the cell then the cell G16 would then show 123 but as the other cell was empty it returns 0

 

[Fluff]

With the formulae you have shown, if G13 is blank then so will H13 & G16.

 

[ipbr21054]

I dont think im getting across clear.
If G13 is blank then H14:H18 is also blank,this is correct.

But the issue lies here.
Select a customer using the drop down list in cell G13
It then looks at the DATABASE sheet for the required information to be returned going by the formula in each cell.
So
G14 =IFERROR(INDEX(DATABASE!R:R,$H$13),"")
G15 =IFERROR(INDEX(DATABASE!S:S,$H$13),"")
G16 =IFERROR(INDEX(DATABASE!T:T,$H$13),"")
G17 =IFERROR(INDEX(DATABASE!U:U,$H$13),"")
G18 =IFERROR(INDEX(DATABASE!V:V,$H$13),"")

So now,
G13 shows ROD SMITH
G14 shows 14 MALLARDS CLOSE
G15 shows BANWELL
G16 shows 0
G17 shows TAUNTON
G18 shows CF1 8VW

On the DATABASE sheet
The row that is assigned to ROD SMITH is as follows
ROW R has 14 MALLARDS CLOSE in the cell
ROW S has BANWELL in the cell
ROW T is empty
ROW U has TAUNTON in the cell
ROW V has CF1 8VW in the cell

So ROW T which has the empty cell then returns a o in the INV cell G16

So why return 0 and not just have it also empty.
I can print the invoice with 0 in between the Name & Address

 

[Fluff]

That's just how the INDEX function works.
If you don't want the 0 then you will need to test for it like
=IFERROR(IF(INDEX(DATABASE!T:T,$H$13)=0,"",INDEX(DATABASE!T:T,$H$13)),"")