Maxif with critera help

[Kellogg]

Dear Great Minds of Excel,

I'm having an issue with the conversion from a cell formula to VBA code. I'm using a User Form to enter data and would like the maximum number that matches a particular identifier. I developed the array formula and it works well. But I'm having a problem converting to vba code attached to a button.

Scenario: I have multiple columns.
Column A equals the entry number.
Column C equals the vehicle Tag number.
Column H equals the total miles.


A C (Tag) H (miles) P
1 A12345 100 A12345
2 A11122 250
3 A12345 175
4 A11222 200
5 A12345 500
6 A11122 300

What I want to do is enter the Tag number and find the maximum miles when I press a button on my user form. So, I enter A12345 into a text field, press a button and get 500. The code below works when in a cell and P1 is the tag number.

{=MAX(IF($C$1:$C$9=$P$1,$H$1:$H$9))} ' This works

My issue is when I translate to VBA code

I have the following:

Dim VehicleTagRange As Range
Dim MaxMilesRange As Range
Dim VehTag
Dim lnglast As Long
Dim Maxif As Variant
Dim TagNmbr
'
'
    lnglast = Range("A" & Rows.Count).End(xlUp).Row
    TagNmbr = "A12345"
'
'
Set VehicleTagRange = Range("C1:C" & lnglast)
Set MaxMilesRange = Range("H1:H" & lnglast)
Set VehTag = "A12345"
'
'
Maxif = Application.Evaluate("=Max(IF(" & VehicleTagRange.Address & "=" & TagNmbr & "," & MaxMilesRange.Address & "))")
'
MsgBox Maxif       'This returns 0
'

'
'
Any suggestion is greatly appreciated. Thanks in advance.

/s/

Craig

 

[Jonmo1]

I think it's seeing TagNmbr as a cell address A12345 (Column A Row 12345)
And wold be evaluated as
=MAX(IF($C$1:$C$9=A12345,$H$1:$H$9))

As a Cell formula, that would have to be written like
=MAX(IF($C$1:$C$9="A12345",$H$1:$H$9))

Need to get those quote marks into the formula..

Try
Maxif = Application.Evaluate("=Max(IF(" & VehicleTagRange.Address & "=""" & TagNmbr & """," & MaxMilesRange.Address & "))")

 

[Kellogg]

Jonmo1,

Thanks a million. That worked. I did not think that the quotes would have been the issue. I appreciate it.

/s/

Craig

 

[Jonmo1]

Glad to help, thanks for the feedback.