I am trying to write some code for Excel that takes the selected value of a
cell on one sheet and searches for it on a second sheet ("Data") in the same
workbook, but within a specific range around the active cell on the second
sheet. My code selects the range on the second sheet without a problem but
errors when attempting to use the find method. Here's the code:

Sub ToData()

Dim SearchTarget As String
Dim RngTop, RngBot As Integer

SearchTarget = Selection 'active cell on sheet 1

Worksheets("Data").Activate
RngRow = ActiveCell.Row

'demarcates range
RngBot = Cells(RngRow, 3).End(xlDown).Row
RngTop = Cells(RngRow, 3).End(xlUp).Row
Set Rng = Range("A" & RngTop, "A" & RngBot)
Rng.Select
'if the last line containing the Find function is omitted, the range is
selected as intended
'when the following Find is added, an error occurs

Rng.Find(What:=SearchTarget, LookAt:= _
xlPart, SearchDirection:=xlNext, MatchCase:=False).Activate

End Sub

Thanks for the help!

Greg

Lots of times, it's better to set a range variable to that foundcell. Then you
can test it later to see if the string was actually found:


Option Explicit
Sub ToData()

Dim SearchTarget As String
Dim RngTop As Long
Dim RngBot As Long
Dim RngRow As Long
Dim Rng As Range
Dim FoundCell As Range

SearchTarget = Selection 'active cell on sheet 1

Worksheets("Data").Activate
RngRow = ActiveCell.Row

'demarcates range
RngBot = Cells(RngRow, 3).End(xlDown).Row
RngTop = Cells(RngRow, 3).End(xlUp).Row
Set Rng = Range("A" & RngTop, "A" & RngBot)
'Rng.Select

'if the last line containing the Find function
'is omitted, the range is selected As intended
'when the following Find is added, an error occurs


With Rng
Set FoundCell = .Cells.Find(What:=SearchTarget, LookAt:=xlPart, _
SearchDirection:=xlNext, _
after:=.Cells(.Cells.Count), _
MatchCase:=False)
End With
If FoundCell Is Nothing Then
MsgBox SearchTarget & " wasn't found in: " & Rng.Address(0, 0)
Else
FoundCell.Activate
'or whatever you want to do.
End If

End Sub


Gregory Turk wrote:

I am trying to write some code for Excel that takes the selected value of a
cell on one sheet and searches for it on a second sheet ("Data") in the same
workbook, but within a specific range around the active cell on the second
sheet. My code selects the range on the second sheet without a problem but
errors when attempting to use the find method. Here's the code:

Sub ToData()

Dim SearchTarget As String
Dim RngTop, RngBot As Integer

SearchTarget = Selection 'active cell on sheet 1

Worksheets("Data").Activate
RngRow = ActiveCell.Row

'demarcates range
RngBot = Cells(RngRow, 3).End(xlDown).Row
RngTop = Cells(RngRow, 3).End(xlUp).Row
Set Rng = Range("A" & RngTop, "A" & RngBot)
Rng.Select
'if the last line containing the Find function is omitted, the range is
selected as intended
'when the following Find is added, an error occurs

Rng.Find(What:=SearchTarget, LookAt:= _
xlPart, SearchDirection:=xlNext, MatchCase:=False).Activate

End Sub

Thanks for the help!

Greg

--

Dave Peterson

Yes, it works! Thank you very much, Dave.

Happy computing.

Greg