Macro clear cell value & also its code

ipbr21054

Well-known Member
Joined
Nov 16, 2010
Messages
5,832
Office Version
  1. 2007
Platform
  1. Windows
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.

Code:
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.
Code:
=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
 
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 ???
 
Upvote 0

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.
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 ""
 
Upvote 0
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
 
Upvote 0
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
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
 
Upvote 0
You will need to put the IFERROR around all the formulae
 
Upvote 0
Sorry but i am unable to even understand that.
I have this but see an error about to many arguements

Code:
=IFERROR(VLOOKUP(DATABASE!R:R,$H$13),"Not found")
 
Upvote 0
Your VLOOKUP formula is wrong.
Dont change the existing formula other than to wrap it in the IFERROR
 
Upvote 0

Forum statistics

Threads
1,224,823
Messages
6,181,182
Members
453,020
Latest member
Mohamed Magdi Tawfiq Emam

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top