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.
<tbody>
</tbody>
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>