Find method issue

[davidam]

Hello All,
Can someone please tell me why I get an error on the fourth line of the following:

Dim name As String
Dim Loc As Range
name = ActiveCell.Offset(Range("A4").Value, 0)
Set Loc = Range("A63500:A65500").Find(What:=name, LookIn:=xlValues)
ActiveCell.Value = Loc.Offset(0, 4).Value

Thank you.

 

[brettdj]

This code caters for your "name" variable not being found

If you do expect it to be in the range then ensure you are matching part strings rather than whole strings if necessary, check for inconsistent whitespaces in the search or result strings etc

Cheers

Dave

  Dim name As String
    Dim Loc As Range
    name = ActiveCell.Offset(Range("A4").Value, 0)
    Set Loc = Range("A63500:A65500").Find(What:=name, LookIn:=xlValues)
    If Not Loc Is Nothing Then
        ActiveCell.Value = Loc.Offset(0, 4).Value
    Else
        MsgBox "value not found"
    End If

 

[davidam]

You are correct...the issue was the value not being found...I need to make sure this is covered.
Thank you!

 

[dk]

You didn't say what the error was but I'd guess that it's because the data is not found. You need to include some error handling in case the value is not located by the Find method e.g.

Dim name As String
Dim Loc As Range
name = ActiveCell.Offset(Range("A4").Value, 0)

On Error Resume Next
Set Loc = Range("A63500:A65500").Find(What:=name, LookIn:=xlValues)
On Error GoTo 0

If Loc Is Nothing Then
    MsgBox "Data not found"
Else
    ActiveCell.Value = Loc.Offset(0, 4).Value
End If

HTH
DK

 

[davidam]

Yes; in this case it will not be a message box since I am just processing big chunks of data, but an error handler is definately in order. Thank you!

 

[Nalani]

I'm not seeing how your code is Compileing:

name = ActiveCell.Offset(Range("A4").Value, 0)

Throws a "Type Mismatch" for me.

From Help:

expression.Offset(RowOffset, ColumnOffset)

 

[davidam]

Range("A4") is the row value and this part of the code is OK. It was an issue of not finding the value in the list. Problem is now solved. Thank you.

 

[davidam]

The active cell is what I am trying to populate. In a cell directly above (the number of rows is the value found in A4) is a string. This string then become the variable "name". I want to hunt for "name" in range "A63500:A6550" which will be at the range called "Loc" and then return a value that is 4 cells over from "Loc". Works now.