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 show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.
The syntax you want is:
Code:
Application.Evaluate("=MAX(ROW(A" & x & ":A120)*(A" & x & ":A120=""SSUS""))")
 
Upvote 0

Forum statistics

Threads
1,213,551
Messages
6,114,267
Members
448,558
Latest member
aivin

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
Back
Top