Aggregate function in VBA Excel

Question

I have table with Product name and Batch No. The product name is selected from a combo box frmmaster.cmbproduct. Each product has a unique combination for batch number and the last digit of the batch number changes. Sample table

The next batch number of a particular product is the last batch number +1. I have used aggregate function in excel to find the highest batch number. But the same aggregate function is throwing error in VBA.

Aggregate function for excel is =AGGREGATE(14,4,($B$2:$B$2000 =N12)*$C$2:$C$2000,1) where in N12 I am putting product name. Column B contains product name and column C contains batch number

Dim res As Long

Dim sh As Worksheet
Set sh = ThisWorkbook.Sheets("Database")
With sh
'On Error Resume Next

res = WorksheetFunction.Aggregate(14, 4, ("R2C2:R2000C2" = FrmMaster.CmbProduct.Value) * "R2C3:R2000C3", 1)
MsgBox (res)

End With

The above code is throwing type mismatch error

I have changed the variable to hold value to variant and string but no result.

Answer 1

The reason for the type mismatch error is that the term ("R2C2:R2000C2" = FrmMaster.CmbProduct.Value) * "R2C3:R2000C3" tries multiplying a boolean value (result of ("R2C2:R2000C2" = FrmMaster.CmbProduct.Value)) with the string literal "R2C3:R2000C3". That of course is a type mismatch.

But there are other issues.

The term "R2C2:R2000C2" = FrmMaster.CmbProduct.Value compares the string "R2C2:R2000C2" to the FrmMaster.CmbProduct.Value. This always gets false except FrmMaster.CmbProduct.Value would be "R2C2:R2000C2". And it is not the same as $B$2:$B$2000=N12 in your array formula. There the $B$2:$B$2000 is a range of cell values which each gets compared to value of N12. But this is nothing what VBA is able to do. Even (sh.Range("$B$2:$B$2000") = FrmMaster.CmbProduct.Value) cannot work because this tries comparing an array (sh.Range("$B$2:$B$2000")) to a single value.

So the only way to evaluate an array formula having that kind of array operations in it is using Evaluate. WorksheetFunction cannot help here since it needs the single function parameters evaluated trough VBA first.

Example solving your problem:

 Dim res As Long
 Dim sh As Worksheet
 Dim range1Address As String
 Dim range2Address As String
 Dim arrayFormula As String

 Set sh = ThisWorkbook.Sheets("Database")
 With sh
  range1Address = .Range("B2:B2000").Address(RowAbsolute:=True, ColumnAbsolute:=True, ReferenceStyle:=xlA1, External:=True)
  range2Address = .Range("C2:C2000").Address(RowAbsolute:=True, ColumnAbsolute:=True, ReferenceStyle:=xlA1, External:=True)
  arrayFormula = "AGGREGATE(14,4,(" & range1Address & "=""" & FrmMaster.CmbProduct.Value & """)*" & range2Address & ",1)"
  res = Evaluate(arrayFormula)
  MsgBox res
 End With

Getting range1Address and range2Address as external addresses (including workbook name and sheet name) from the ranges makes this independent of the active sheet.

Answer 2

I wonder if the formula below will be of help to you. It will return whatever number it finds associated with the lookup value +1.

=IFERROR(LOOKUP(2,1/($A1:$A$1=A2),$B1:$B$1),0)+1

First, the functionality. The function looks for the last occurrence of A2 above A2 which is in a range starting at absolute $A$1 and ending in relative $A1. The latter will expand as the formula copied down. Same system in column B. If nothing is found and error occurs, and if an error occurs the function returns 0. To the result of this 1 is added.

Now, if you would replace the 0 in the formula (which is the result in case of no precedent) with a number like 2022050 the formula would then return the correct result in the first row of your example and for all subsequent occurrences of "Amoxycillin Capsules 500".

It would return 1 on the line of Paracetamol tablets because there is no precedent. The 1 would stick out and perhaps you have a system by which you can fill in the missing numbers there.

The problem would be the same, regardless of whether you apply VBA or a worksheet function. You need a seed number to base increments on. All considered, the worksheet function looks easier to implement.