VBA: How do I find a value within a selected range?

[Jayne22]

I have declared a range in a prior statement, selected that range, and now
want to find a value within that range. For some reason, the program is
looking outside of that range. What am I doing wrong?

With ActiveCell
Set range1 = Range(ActiveCell, .Offset(100, 0))
End With

Range(range1.Address).Select

Cells.Find(What:=Location, After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate

 

[Jim Thomlinson]

You define your range. You select the range (which is not necessary) and then
you use Cells wich completely ignores the selection and searches the whole
sheet. If the location is not fond your code will then crash as you are
trying to activate a cell that does not exist... Try this...

dim range1 as range
dim rngFound as range

Set range1 = Range(ActiveCell, activecell.Offset(100, 0))
set rngfound = range1.find(What:=Location, LookIn:=xlFormulas, _
LookAt:=xlPart, MatchCase:=False)

if rngfound is nothing then
msgbox "location was not found"
else
rngFound.select
end if

 

[sbitaxi]

I have declared a range in a prior statement, selected that range, and now
want to find a value within that range. For some reason, the program is
looking outside of that range. What am I doing wrong?

With ActiveCell
            Set range1 = Range(ActiveCell, .Offset(100, 0))
End With

Range(range1.Address).Select

Cells.Find(What:=Location, After:=ActiveCell, LookIn:=xlFormulas, _
            LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
            MatchCase:=False, SearchFormat:=False).Activate

Hi Jayne:

Try changing your find statement to

range1.Find(...

However, if you are using a Loop or For/Next statement, your range may
change if the ActiveCell statement is within the Loop or For/Next
because your .Find activates the new cell.

HTH,

Steven