Running a macro without deleting Vlookup

PEAKCAB

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

Some videos you may like

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
46,530
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
46,530
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
46

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
46,530
Office Version
  1. 365
Platform
  1. Windows
Have a look at the IFNA function
 

PEAKCAB

New Member
Joined
Jul 16, 2018
Messages
46

ADVERTISEMENT

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

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
46,530
Office Version
  1. 365
Platform
  1. Windows
You're welcome
 

Watch MrExcel Video

Forum statistics

Threads
1,109,341
Messages
5,528,146
Members
409,802
Latest member
joeino

This Week's Hot Topics

  • Change military grades into rank
    Afternoon all Need help with formula that will change military rank (i.e. 1, 2, 3 into Amn, A1C, SrA). Running IF formula that does not work...
  • VBA COUNTIF SOLUTION
    Hi The following are the errors spread across the several columns from E to Q ie. 13 columns across several sheets with more than 500 rows per...
  • INSERT ROW WITH SPECIFIS TEXT IN A COLUMN
    Hi All! How can identify that that the row to be inserted has to be inserted before 1st row with specific text in column F. If I record the...
  • Auto-Create a monthly Sign in sheet for preschool students
    The image below is what each page looks like. Above is space for the "Child Name" "Month" "Class" School days are obviously Monday-Friday but...
  • VBA vlookup multiple results
    Hi folks, Hopefully someone out there can help. I have a list to vlookup which works (ish). the lookup only picks up the first instance of the...
  • Extract values for earliest/latest times
    I am trying to put together a formula to get the earliest start time, the latest end time from column A for each person in Column B-F without the...
Top