1

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}
Uvulagirl
  • 17
  • 1
  • 7

3 Answers3

1

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
Dom
  • 83
  • 6
  • Thanks @Dom! That code has worked! I knew it would be something simple that I just couldn't think of. Though I'm using Excel 2016 on my laptop at home, at work we're using 2010. Will that affect this code from working? cheers – Uvulagirl Oct 17 '18 at 11:34
  • No problem happy to help. Take a look at Vityata’s answer - I think enabling MS Office 16.0 object library would work, although I’m not sure if this is available in Excel 2010. – Dom Oct 17 '18 at 11:40
  • Hi @Dom, bad news. It looks like MS Office 16.0 object library isn't available in my work's set up. Are you able to suggest a different code? I can get array formula =MAX(IF(A:A<2018000,A:A)) +1 to get the result I want in Excel but I've never used an array formula in VBA. – Uvulagirl Oct 18 '18 at 00:36
1

...but apparently MAXIFS is not an available function in VBA. In VBA it is not present, but if you add the Excel 16.0 Object Library (second on the screenshot) to your project, you would be able to access it as follows:

Application.WorksheetFunction.MaxIfs 'Only in Excel
Excel.WorksheetFunction.MaxIfs       'Any host of VBA - Excel, Access, Word

enter image description here

The library is added by default, if you work in Excel. Concerning "translation" the working formula from Excel to VBA, check this:

https://stackoverflow.com/a/49363501/5448626

Vityata
  • 42,633
  • 8
  • 55
  • 100
  • 1
    Thanks @Vityata! That is very valuable information. I will have to try it out next time I get stuck converting formulas to VBA. Cheers! – Uvulagirl Oct 17 '18 at 11:39
0

I just learned the Evaluate function! So equivalent of the array formula I want to use converts in VBA to:

Me.tbContractNumber = Evaluate("=MAX(IF(" & "A:A" & "<2018000," & "A:A" & "))+1")
Uvulagirl
  • 17
  • 1
  • 7