Worksheet Function Max with condition

Question

I have a sheet that has several columns, what i am after is to display the highest number in column A if column D matches the name from the textbox.

This is the formula i got to work on the sheet, to display the last number used.

=MAX(INDEX((D2:D41=L11)*B2:B41,0))

The problem i am having, is i cant get this to convert into VBA

'Cells with dates also return a value, and get covered for determining largest value. Percentages will convert and return numerics.

Dim rngVal As Range
Dim rngName as range
Dim Max As Double
Dim Name As String

'Set range from which to determine largest value
Set rngVal = sheets("Payment History").Range("B2:B41")
Set rngName = sheets("Payment History").Range("D2:D41")
Name = Me.TextBox1.value

'Worksheet function MAX returns the largest value in a range
Max = Application.WorksheetFunction.Max(rngVal)

'Displays largest value
MsgBox Max
End Sub

This is the code that i have, which is displaying the max number off all values.

Answer 1

As an easy way to "copy" formulas to vba-code you can use the Evaluate function. For your example, it would look like this:

Public Sub Test()
  Dim rngVal As String
  Dim rngName As String
  Dim xMax As Double
  Dim xName As String

  'Set range from which to determine largest value
  rngVal = Sheets("Payment History").Range("B2:B41").Address
  rngName = Sheets("Payment History").Range("D2:D41").Address
  xName = Me.TextBox1.Value

  'Worksheet function MAX returns the largest value in a range
  xMax = Evaluate("MAX(IF(" & rngName & "=" & xName & "," & rngVal & "))")

  'Displays largest value
  MsgBox xMax
End Sub

Hint: Do not use Name or Max because they are properties of objects and sometimes VBA gets confused if the same "name" connects to different object-types :)