You are here:

Excel/Convert excel Max function to VBA formula


Hi. Could you please assist with coding to retrieve the following records by using VBA code? I can achieve this by using MAX excel function but would prefer using VBA code.

The (Service History) keeps a record of the service history of about 800 vehicles. What I would like to achieve is to retrieve the last service date of ALL vehicles in the database and put it in a new worksheet.

Example: (extraction)

Worksheet: (Service History)
Week Year    Date       Registration    Kilometres    Service Done

48 2013    20 Nov 2013    AB 123456    78 945          A

49 2013    27 Nov 2013    BC 789123    23 654          B

03 2014    21 Jan 2014    AB 123456    88 000          B

06 2014    13 Feb 2014    BC 789123    34 567          C

20 2014    25 May 2014    AB 123456    100 000          C

22 2014    6 June 2014    BC 789123    49 000          A

This is what I would like to have in the :-

New Worksheet (Service Summation)

Week Year    Date       Registration    Kilometres    Service Done

20 2014    25 May 2014    AB 123456    100 000          C

22 2014    6 June 2014    BC 789123    49 000          A

I can achieve this by using this code in an excel cell which is far too slow. Is there any chance I can adapt it to VBA formula function?

=IFERROR(INDEX(ServiceHistoryRange,MATCH(MAX(IF(ISERROR(SEARCH($A2,ServiceHistoryRegistration)),"",ServiceHistoryDate)),IF(ISERROR(SEARCH($A2,ServiceHistoryRegistration)),"",ServiceHistoryDate),0),COLUMN('Service History'!$B$1)),"")



send a sample file to  

(You don't need to have service records for 800 vehicles in it - only enough so I can test the code and understand what you are doing - but make sure the named ranges are in the file).

since you are using defined names/named ranges in your formula, I have no way of knowing what those refer to.

If I can see your file with the named ranged defined, then I will have a better idea of what you are asking.  

Also, do you want the whole row copied to another sheet?  If not, what do you want copied.  
About Excel
This topic answers questions related to Microsoft Excel spreadsheet (or workbook) stand-alone or Mircrosoft Office Excel including Excel 2003, Excel 2007, Office 2000, and Office XP. You can get Excel help on Excel formulas(or functions), Excell macros, charting in Excel, advanced features, and the general use of Excel. This does not provide a general Excel tutorial nor the basics of using a spreadsheet. It provides specific answers to using Microsoft Excel only. If you do not see your Excel question answered in this area then please ask an Excel question here


All Answers

Answers by Expert:

Ask Experts


Tom Ogilvy


Selected as an Excel MVP by Microsoft since 1999. Answering Excel questions in Allexperts since its inception in 2001. Able to answer questions on almost all aspects of Excel's internal capabilities. If seeking a VBA solution, please specify that in your question itself so I give you the answer you want. [Excel has weak protection - if you are distributing an application, I don't answer questions on how to protect your project from your users.]


Extensive experience.

Master of Science (MS) degree Operations Research (ORSA)

Awards and Honors
Microsoft MVP in Excel.

©2016 All rights reserved.