Running a macro without deleting Vlookup

PEAKCAB

New Member
Joined
Jul 16, 2018
Messages
48
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! :)
 

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
74,442
Office Version
  1. 365
Platform
  1. Windows
Hi & welcome to MrExcel
Which cells are your formulae in?
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
74,442
Office Version
  1. 365
Platform
  1. Windows
In that case change
Code:
Range("A14:F20").ClearContents
to
Code:
Range("B14:E20").ClearContents
 

PEAKCAB

New Member
Joined
Jul 16, 2018
Messages
48

ADVERTISEMENT

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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
74,442
Office Version
  1. 365
Platform
  1. Windows
Have a look at the IFNA function
 

PEAKCAB

New Member
Joined
Jul 16, 2018
Messages
48

ADVERTISEMENT

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

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
74,442
Office Version
  1. 365
Platform
  1. Windows
You're welcome
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,167,298
Messages
5,853,136
Members
431,549
Latest member
NnAa

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
Top