Problem with .Evaluate syntax using the ampersand operator

pennypacker

New Member
Joined
Apr 20, 2010
Messages
9
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 AColumn B
SSUS11
SSUS123
SSUS12

<tbody>
</tbody>
 

Some videos you may like

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
35,503
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
The syntax you want is:
Code:
Application.Evaluate("=MAX(ROW(A" & x & ":A120)*(A" & x & ":A120=""SSUS""))")
 

pennypacker

New Member
Joined
Apr 20, 2010
Messages
9
Thank you so much RoryA, from your corrected syntax I think I see the reasoning behind it.
 

Watch MrExcel Video

Forum statistics

Threads
1,108,970
Messages
5,525,965
Members
409,673
Latest member
Riseee

This Week's Hot Topics

Top