Unable to Search using Application.WorksheetFunction.Match and Range with Variables

[TrickyRick]

Using 2013 VBA.

I am trying to search a Range, i.e. D2:D100 for a string. String to match is a Sting Variable that changes, i.e. TktToFind = "100001-". Object is to return the Row to a Long Variable RowCount.

The Range changes so the beginning and ending are contained in string variables, i.e. BeginCell = "D2", EndCell = "D100"

The command used throws error '1004', Method 'Range' of object '_Worksheet' failed.

The Sheet containing the rows to search is set Active.

Private Sub FindRecord()
Dim RowFound As Long
RowFound = Application.WorksheetFunction.Match(TktToFind, Range(BeginCell, EndCell), 0)

Assistance is appreciated.

 

[chicagocomputerclasses]

try using Application.Match instead and/or do error handling before you run your match function like "On Error Resume Next"

in addition define a variable as a range first and then assign a range to that variable and use it inside match. you may also need to define the worksheet/workbook the range is in depending on where you run the macro from.

so something like

Dim RG as Range

RG = range(BeginCell & ":" & EndCell)

 

[jasonb75]

Or use Find instead of Match

Dim rFound As Range
Set rFound = Range(BeginCell, EndCell).Find(TktToFind, , xlValue, xlWhole)
If Not rFound Is Nothing Then RowFound = rFound.Row - Range(BeginCell).Row + 1

 

[TrickyRick]

Ok, first the confession! I had the BeginCell and EndCell defined in the wrong place.

After fixing that, if I use either Range(BeginCell, EndCell) or Range(BeginCell & ":" & EndCell) I get Unable to get the Match property of the WorksheeFunction class

I also tried using Set SearchRange = Range(BeginCell, EndCell with Dim SearchRange as Range, and using SearchRange in the statement.

 

[chicagocomputerclasses]

Is the start and end range in the same column?
Match can only look through it if the range is a single column.
Also have you tried Application.Match instead?

 

[jasonb75]

Can you post all of your code rather than just the lines you think are causing the problem, sometimes a simple typo elsewhere in the code can cause errors elsewhere.

 

[Norie]

The 'Unable to get...' error usually means what you are looking for isn't being found where you are looking for it.