i = row number of last occurance of duplicate string Column A

[L. Howard]

In the code, trying to assign the row number this formula returns to i.
=LOOKUP(2,1/(A1:A20="abc"),ROW(A1:A20)) = 15 (on the sheet Array Enter)

Errors out with "OneRng" in this line highlighted in blue (OneRng = "abc")
Code compiles, but with "expanded" spaces?? Then errors when run.

Sub SomeRangeA()
Dim OneRng As Range
Dim i As Long, ii As Long

Set OneRng = Range("A1:A" & Cells(Rows.Count, "A").End(xlUp).Row)
i = Application.WorksheetFunction.Lookup(2, 1 / (OneRng = "abc"), Row(OneRng))

End Sub

Thanks,
Howard

 

[Claus Busch]

Hi Howard,

Am Tue, 4 Nov 2014 04:14:39 -0800 (PST) schrieb L. Howard:

Set OneRng = Range("A1:A" & Cells(Rows.Count, "A").End(xlUp).Row)
i = Application.WorksheetFunction.Lookup(2, 1 / (OneRng = "abc"), Row(OneRng))

try:
Sub SomeRangeA()
Dim lRow As Long
Dim i As Long

lRow = Cells(Rows.Count, "A").End(xlUp).Row
i = Evaluate("=Lookup(2,1/(A2:A" & lRow & " = ""abc""), Row(A2:A" & lRow & "))")
End Sub


Regards
Claus B.

 

[L. Howard]

Hi Howard,

Am Tue, 4 Nov 2014 04:14:39 -0800 (PST) schrieb L. Howard:


try:
Sub SomeRangeA()
Dim lRow As Long
Dim i As Long

lRow = Cells(Rows.Count, "A").End(xlUp).Row
i = Evaluate("=Lookup(2,1/(A2:A" & lRow & " = ""abc""), Row(A2:A" & lRow & "))")
End Sub


Regards
Claus B.
--

Thanks Claus, works well.

Is there a short answer as to why Application.WorksheetFunction.... does not work?

Howard

 

[Claus Busch]

Hi Howard,

Am Tue, 4 Nov 2014 06:44:41 -0800 (PST) schrieb L. Howard:

Is there a short answer as to why Application.WorksheetFunction.... does not work?

I don't know. If the syntax is correct I get a Type Mismatch.

But you can also try it with MATCH:

Sub Test()
Dim i As Long, LRow As Long

LRow = Cells(Rows.Count, 1).End(xlUp).Row
With WorksheetFunction
i = .Match("""abc""", Range("A1:A" & LRow), -1)
End With
MsgBox i
End Sub



Regards
Claus B.

 

[L. Howard]

Hi Howard,

Am Tue, 4 Nov 2014 06:44:41 -0800 (PST) schrieb L. Howard:


I don't know. If the syntax is correct I get a Type Mismatch.

But you can also try it with MATCH:

Sub Test()
Dim i As Long, LRow As Long

LRow = Cells(Rows.Count, 1).End(xlUp).Row
With WorksheetFunction
i = .Match("""abc""", Range("A1:A" & LRow), -1)
End With
MsgBox i
End Sub



Regards
Claus B.

Okay, appreciate the info.

Your first suggestion works quite well and I am using it.

Thanks again.

Regards,
Howard