Problem with .Evaluate syntax using the ampersand operator

pennypacker

New Member
Joined
Apr 20, 2010
Messages
10
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>
 

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
38,782
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
10
Thank you so much RoryA, from your corrected syntax I think I see the reasoning behind it.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,017
Messages
5,834,960
Members
430,330
Latest member
drAli77

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top