Find Last row number for Col matching Pattern

[AlexanderBB]

I want to use this is a Function and get the last row number where the first 4 chars in column "Type" are "1990". Format is General
I'm trying this but get subscript out of range
= ThisWorkbook.Sheets("Sheet1").Range("Type").Find(what:="1990*", LookAt:=xlPart, lookIn:=xlValues, SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row

Thanks for any correction.

 

[Phuoc]

Try change LookAt:=xlPart to LookAt:=xlWhole

 

[Trebor76]

That error message is usually generated when you're trying to reference a sheet tab that doesn't exist. Double check what tab the named range "Type" is on, change Sheet1 to suit and try again.

 

[AlexanderBB]

Sheet Name and Column name are confirmed.
Changing to xlWhole alters the error to Run-time error '1004': Application-defined or object-defined error
But oddly, changing back to xlPart keeps the same error.

 

[Trebor76]

Make sure the named range "Type" exists. If it doesn't - create it and if does make sure it's not in error.

 

[AlexanderBB]

"Type" is the name of the Column B - the text in Row 1. Is that the "named range" ?
Or should it be Set Type = Range("B1:B100")
Although 1-100 is a guess, ideally it'd be every row in the worksheet.

 

[Trebor76]

In your code...

ThisWorkbook.Sheets("Sheet1").Range("Type").Find(what:="1990*", LookAt:=xlPart, lookIn:=xlValues, SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row

...Type is expected to be a named range which are set via the following path Formulas > Define Name. I think you just need this:

Option Explicit
Sub Macro1()

    Dim lngLastRow As Long

    lngLastRow = ThisWorkbook.Sheets("Sheet1").Range("B1:B100").Find(what:="1990*", LookAt:=xlWhole, LookIn:=xlValues, SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row

End Sub

Regards,

Robert

 

[AlexanderBB]

Thanks Robert, that's nailed it. Range definition was the problem. I found Range("B:B") would do every row.

 

[Trebor76]

Thanks for letting us know and you're welcome