Vba clear contents

[Lizzy706]

I am extremely new to using VBA and by no means in the first flush of youth! All replies must be simple!
I have created an excel invoice and used VBA code to increment the invoice number for each new invoice raised and also when doing this have a specified certain cells in which I wish to ClearContents when going to the next new invoice number. All is working well to this point.
I want to also clear the contents of cells D26:D32 but these cells have a formula {VLOOKUP}which I do not wish to clear. I have looked on Google and must say I am more confused than ever. Please can someone help me

 

[JoeMo]

I am extremely new to using VBA and by no means in the first flush of youth! All replies must be simple!
I have created an excel invoice and used VBA code to increment the invoice number for each new invoice raised and also when doing this have a specified certain cells in which I wish to ClearContents when going to the next new invoice number. All is working well to this point.
I want to also clear the contents of cells D26:D32 but these cells have a formula {VLOOKUP}which I do not wish to clear. I have looked on Google and must say I am more confused than ever. Please can someone help me

Unfortunately, clear contents removes formulas too. If you want the cells holding those formulas to appear blank, post the formulas you are using, tell us under what conditions you want them to appear blank, and we can help revise them accordingly.

 

[Joe4]

Welcome to the Board!

So, is the VLOOKUP dependent upon one of the fields that you are clearing? If so, instead of clearing D26:D32, how about just amending the formula to not return anything if the cell it is looking at is blank?

For example, let's say that the VLOOKUP is looking up the value in cell C10, and your script is clearing the value in C10. Just amend your VLOOKUP formula like this:

=IF(C10="","",VLOOKUP(...))

Then there is no need to clear the contents from these VLOOKUP formula cells.

 

[dmt32]

Hi,

Make backup of your template & then add these lines of code in to your project where appropriate

        On Error Resume Next
        Range("D26:D32").Cells.SpecialCells(xlCellTypeConstants).ClearContents
        On Error GoTo 0

and see if this does what you want (hopefully!).

Dave

 

[Lizzy706]

Welcome to the Board!

So, is the VLOOKUP dependent upon one of the fields that you are clearing? If so, instead of clearing D26:D32, how about just amending the formula to not return anything if the cell it is looking at is blank?

For example, let's say that the VLOOKUP is looking up the value in cell C10, and your script is clearing the value in C10. Just amend your VLOOKUP formula like this:

=IF(C10="","",VLOOKUP(...))

Then there is no need to clear the contents from these VLOOKUP formula cells.

Hi Joe
Thanks very much for your reply I do really appreciate it.
Cells D24:D32 are customers names and addresses all based around a VLOOKUP customer list/database. In cell D25 I have a dropdown box which identifies the customer surname and when selected fills cells D24 and D26:D32 with the relevant address post code phone number etc etc.
When I click on my box to create a new invoice number I would like it to clear the previous invoice customer details perhaps just leaving the surname in D25 from which to select the next new customer for the next invoice.
Lizzy

 

[JoeMo]

Hi Joe
Thanks very much for your reply I do really appreciate it.
Cells D24:D32 are customers names and addresses all based around a VLOOKUP customer list/database. In cell D25 I have a dropdown box which identifies the customer surname and when selected fills cells D24 and D26:D32 with the relevant address post code phone number etc etc.
When I click on my box to create a new invoice number I would like it to clear the previous invoice customer details perhaps just leaving the surname in D25 from which to select the next new customer for the next invoice.
Lizzy

Why not include D25 in the range for which you clear contents? Then you can wrap your Vlookups in an IFERROR function to return "" ( a zero-length string - looks like a blank cell) to the formula cells when the dropdown cell is clear.