Problem with .Evaluate syntax using the ampersand operator

[pennypacker]

Hello,

I am working with Excel 2010 and would like to replicate the following formula which finds the row number of the cell with the last instance of the string "SSUS" in a range in column A, but instead of using numbers for the rows like the formula below I would like, if possible, to use a variable. The following array formula works fine :
MsgBox Application.Evaluate("=MAX(ROW(A86:A120)*(A86:A120=""SSUS""))").

but when I try to modify it and replace the 86 and 120 numbers by variables it fails, I use the following varialbe which gives me in essence the first instance of the word SSUS in column A and it also works.

The variable, let's call it x, is assigned the value "ThisWorkbook.Worksheets("Sheet1").UsedRange.Find(What:="SSUS", LookIn:=xlValues).Row" and it is well defined. I have tried to incorporate it into the formula in the following way:

Application.Evaluate("=MAX(ROW(A&x:A120)*(A&x&:A120=""SSUS""))")

but this returns an error i.e. MsgBox Application.Evaluate("=MAX(ROW(A&x:A120)*(A&x&:A120=""SSUS""))") , the error is a Run-time error '13' : Type mismatch, I am not sure if this is a syntax error or something else. Any ideas how to fix this using the variable and not some other way? Many thanks in advance for your time and attention.

Column A	Column B
SSUS	11
SSUS	123
SSUS	12

<tbody>
</tbody>

 

[RoryA]

The syntax you want is:

Application.Evaluate("=MAX(ROW(A" & x & ":A120)*(A" & x & ":A120=""SSUS""))")

 

[pennypacker]

Thank you so much RoryA, from your corrected syntax I think I see the reasoning behind it.