excelvbamaxifs

MS Excel 2010 MAXIFS equivalent in VBA Userform


I've created an Excel Userform to facilitate the data entry of new lines into a contract register. I have a field that auto-generates a new unique contract number by looking for the largest number in the list of contract numbers (Column A) and then adds 1. This formula works perfectly:

Me.tbContractNumber = Application.WorksheetFunction.Max(Sheet1.UsedRange.Columns(1)) + 1

I now have to add an IF criteria to filter out any contract numbers LESS Than "2018000". I have worked out how to do this in a normal Excel workbook using MAXIFS but apparently MAXIFS is not an available function in VBA?

Can someone suggest an equivalent VBA code to the below Excel formula? Thanking you in advance!

=MAXIFS(A2:A500,A2:A500,"<2018000")+1

EDIT Our work computers run 2010 and won't allow me to add the MS Office 16.0 Object Library so MAXIFS function will not work. I can get the following array formula to work but I have never used an array formula in VBA. Could someone please suggest an equivalent VBA code to the below Excel formula? Thanking you in advance!

{=MAX(IF(A:A<2018000,A:A)) +1}

Solution

  • If you're looking for the largest number anyway, do you need to filter out anything lower than 2018000? If you have at least one entry equal to/higher than 2018000, your end result will be higher regardless of the other entries.

    I'm sure there are more efficient ways of doing it, but if you are happy with:

    Me.tbContractNumber = Application.WorksheetFunction.Max(Sheet1.UsedRange.Columns(1)) + 1
    

    then try:

    me.tbContractNumber = Application.WorksheetFunction.MaxIfs(Sheet1.UsedRange.Columns(1), Sheet1.UsedRange.Columns(1), ">" & 2018000) + 1