VBA Combining Max IF and Lookup

[LadyDisdain]

I am pretty new to VBA and have been tasked with creating a rather complex email reminder system.

We have a list of equipment which have unique IDs. Each row has various details relating to that equipment ID such as calibration instructions, dates calibrations are due, last calibrated date, etc.

I have written a code that looks at a column called Email Reminder Date and sends an email with details for each piece of equipment who's email reminder date equals today, or is less than today. Once the email is sent, a new sheet (Email Log) is populated with the Equipment ID, the date and time that the email was sent (Timestamp of reminder email), and the date that was in the Email Reminder Date (CalRem) column at the point of sending the email (Reminder date at point of last email)

I only want this email to send if there hasn't already been an email sent for this piece of equipment and reminder date, so I need to lookup the Equipment ID in the Email Log sheet, find the most recent date in the Timestamp of reminder email column, and then compare the corresponding Reminder date at point of last email with the value currently in the Email Reminder Date (CalRem) column. If there is no existing log for that equipment ID, or the reminder date is newer than the last one logged, then an email reminder is sent.

The equipment IDs in the email log cannot be sorted alphabetically, they need to remain in the order they are logged. I can't take the actual workbook home with me, so I'm working on paper printouts - trying to keep typing to a minimum so won't include the full code but I can expand if needed.


Current rule for identifying Equipment IDs which I need to expand upon (there may be multiple rows that match the criteria):

1LastRow = Cells(Rows.Count, 1).End(x1Up).Row
For 1Row 2 To lLastRow
If Cells(1Row, (CNBH("Calibration Reminder Date"))) = Date And Cells(1Row, (CNBH("Available"))) = True Then

'Send email with details such as due date, calibration instructions, etc. Example:
EquipID = Cells(1Row, (CNBH("Equipment ID")))
CalRem = Cells(1Row, (CNBH("Calibration Reminder Date")))

(CNBH refers to a function which enables me to refer to things by column names)


When all the emails are sent, the Email Log sheet is populated:

'1 = Equipment ID
'2 = Timestamp of reminder email
'3 = Reminder date at point of last email
LastLog = Sheets("Email Log").ListOnjects("EmailLog").ListRows.Count + 1
Sheets("Email Log").Cells(LastLog + 1, 1) = EquipID
Sheets("Email Log").Cells(LastLog + 1, 2) = Now()
Sheets("Email Log").Cells(LastLog + 1, 3) = CalRem

I am able to return the max value using the following:

myVar = Evaluate("MAX(IF(A:A=""ABC001"",B:B))")

Problem one: I need "ABC001" to be the equipment ID (EquipID), but can't get this to work.
Problem two: I can lookup "ABC001", but it returns the first match it finds. I need to combine it with the max code.


Any help would be much appreciated, I've been going round and round in circles trying different codes I've found online.

 

[JLGWhiz]

You need the sheet reference

Sub t()
Dim myVar As Long
myVar = Evaluate("MAX(IF([COLOR=#daa520]Sheet1![/COLOR]A:A=""ABC001"",[COLOR=#daa520]Sheet1![/COLOR]B:B))")
End Sub

If you want to use the EquipID variable then you would need to concatenate it into the statement

Sub t()
Dim myVar As Long
myVar = Evaluate("MAX(IF(Sheet1!A:A=" & EquipID & ",Sheet1!B:B))")
End Sub

 

[LadyDisdain]

I tried concatenating before, but it returns a zero value which appears as 30 December 1899 if I show it with a message box

Dim myVar2 As Date
     myVar2 = Evaluate("MAX(IF('Email Log'!A:A=" & EquipID & ",'Email Log'!B:B))")
     MsgBox "Last calibration reminder email for " & EquipID & " was sent " & Format(myVar2, "dd mmmm yyyy" & " at " & "hh:mm")

If I replace EquipID with text, it works:

myVar2 = Evaluate("MAX(IF('Email Log'!A:A=""ABC002"",'Email Log'!B:B))")

 

[JLGWhiz]

I tried concatenating before, but it returns a zero value which appears as 30 December 1899 if I show it with a message box

Dim myVar2 As Date
     myVar2 = Evaluate("MAX(IF('Email Log'!A:A=" & EquipID & ",'Email Log'!B:B))")
     MsgBox "Last calibration reminder email for " & EquipID & " was sent " & Format(myVar2, "dd mmmm yyyy" & " at " & "hh:mm")

If I replace EquipID with text, it works:

myVar2 = Evaluate("MAX(IF('Email Log'!A:A=""ABC002"",'Email Log'!B:B))")

I spent some time on this trying to determine why the variable is returning 0 as a value when a variable is used for the ID number. I couldn't find any articles that gave a good explanation, but I have surmized that the underlying code for the Evaluate function does not support variables in a worksheet formula format. Again, I have no way of proving that point, other than the fact that a hard code value works and the variable does not. Further, I can offer no work around at this juncture.

 

[LadyDisdain]

Thanks for your help, JLGWhiz.

Is anyone else able to offer an alternative way of achieving what I am after?

 

[JLGWhiz]

Thanks for your help, JLGWhiz.

Is anyone else able to offer an alternative way of achieving what I am after?

Rick Rothstein is not on line right now, but he uses the Evaluate function frequently and is quite adept in how it operates. If he would happen to pick up on the thread, he could probably resolve the issue pretty quickly.

 

[LadyDisdain]

I think I've now solved problem one with the following:

 Dim myVar2 As Date
                    myVar2 = Evaluate("MAX(IF('Email Log'!A:A=""" & EquipID & """,'Email Log'!B:B))")
                    MsgBox "Last calibration reminder email for " & EquipID & " was sent " & Format(myVar2, "dd mmmm yyyy" & " at " & "hh:mm")

I'm now wondering if I really need to be combining Lookup with the MAXIF function, or if I can achieve my desired outcome with the MAXIF code alone. I shall get back to yous!

 

[LadyDisdain]

I think I've now solved problem one with the following:

 Dim myVar2 As Date
                    myVar2 = Evaluate("MAX(IF('Email Log'!A:A=""" & EquipID & """,'Email Log'!B:B))")
                    MsgBox "Last calibration reminder email for " & EquipID & " was sent " & Format(myVar2, "dd mmmm yyyy" & " at " & "hh:mm")

I'm now wondering if I really need to be combining Lookup with the MAXIF function, or if I can achieve my desired outcome with the MAXIF code alone. I shall get back to yous!

Having thought it through, I do still need to find a solution for problem two!

I have found the most recent date in the sheet Email Log (in column 2, Timestamp of reminder email) for a particular EquipID using the MAXIF code above, now I need to find the corresponding value in column 3 (called Reminder date at point of last email). Would this be a lookup?