Running a macro without deleting Vlookup

[PEAKCAB]

Hi,

Can anyone help? I am new to all this and I am running a invoice (with macro next number and clear contents etc) and wish to have a vlookup for my customers address. I have done this but when i run the macro this clears all the vlookups.

Sub NextInvoice()
Range("I22").Value = Range("I22").Value + 1
Range("A14:F20").ClearContents
Range("G147:I173").ClearContents
Range("A22").ClearContents
Range("C22").ClearContents
Range("E22").ClearContents
Range("I53").ClearContents
Range("F27:F51").ClearContents
Range("A25:A50").SpecialCells(xlCellTypeConstants, 23).ClearContents
Range("G25:I51").SpecialCells(xlCellTypeConstants, 23).ClearContents
Range("B25:B51").SpecialCells(xlCellTypeConstants, 23).ClearContents

End Sub

The cell A22 refers to a drop down list of the account number from where the vlookup is created and the cells A14:F20 refer to the delivery and invoice addresses.


Any help would be greatly appreciated.....its driving me mad!

 

[Fluff]

Hi & welcome to MrExcel
Which cells are your formulae in?

 

[PEAKCAB]

Hi & welcome to MrExcel
Which cells are your formulae in?

Hello Fluff,

the formulae are in cells A14:A20 & F14:F20

 

[Fluff]

In that case change

Range("A14:F20").ClearContents

to

Range("B14:E20").ClearContents

 

[PEAKCAB]

Hello Fluff,

Many thanks for the response. When i now run the macro, the cell clear as you said but i am left with a #N/A code in the cells. Is there a way of the cell being blank?

Best Regards

 

[Fluff]

Have a look at the IFNA function

 

[PEAKCAB]

I have the following =VLOOKUP($A$22, Account!1:1048576,3,FALSE)

 

[Fluff]

You'll need to wrap that in an IFNA function.
https://exceljet.net/excel-functions/excel-ifna-function

 

[PEAKCAB]

Thanks Fluff.............absolutely brilliant

 

[Fluff]

You're welcome