Error when trying to Find in Range

  • Thread starter Thread starter Jim Youlong
  • Start date Start date
J

Jim Youlong

Hi.

I am trying to do a find in a range. Essentially, I want to find the
value of a cell on one page in a specific range on a second page. When
I incorporate a Range object (i.e. Rng) in to the code and I start
getting errors. Any assistance someone might give would be greatly
appreciated.

Here's a simplified version of the code.

Sub MainPageSearchRange()
Dim SearchTarget As String
Dim RngTop, RngBot As Integer
Dim SlctCll As String

SearchTarget = ActiveWindow.Selection.Cells.Value

Worksheets(1).Activate
Set InRng = ActiveCell
RngRow = InRng.Row
'range demarcated by spaces in a specific column
RngBot = Range("I" & RngRow).End(xlDown).Row
RngTop = Range("I" & RngRow).End(xlUp).Row
Rng = Range("G" & RngTop, "G" & RngBot)
Rng.Find(What:=SearchTarget, LookAt:= _
xlPart, SearchDirection:=xlNext, MatchCase:=False).Execute
ActiveCell.Select
End Sub

Thanks

Greg
 
Looks over complicated. Simpler.

SearchTarget = Selection
Worksheets(1).Activate 'don't have to do that.
RngRow = activecell.Row

How do we know at what cell we will arrive when we get to sheet1?
 
Don,

Thanks for the help.

I took your suggestions and produced the following code.
Unfortunately, it still generates an error. As for your question, how
do we know which cell we arrive at on Sheet(1)[now retitled "Data"], I
will try to explain as best I can. We start off by selecting a cell on
the "Data" sheet, which is used as an object in an entirely different
subroutine to take us to a second sheet. On this sheet, I would like
to give the user the option to select any value and search for it on
the first sheet ("Data") in a range around the original cell selected.
I wonder if that makes sense. Anyway, here is the code

Sub Test()

Dim SearchTarget As String
Dim RngTop, RngBot As Integer

SearchTarget = Selection 'on the second sheet

RngRow = Worksheets("Data").ActiveCell.Row 'original cell selected
before
'before arriving at the
'second sheet

'range demarcated by spaces in a specific column
RngBot = Range("I" & RngRow).End(xlDown).Row
RngTop = Range("I" & RngRow).End(xlUp).Row
Rng = Range("G" & RngTop, "G" & RngBot)
Rng.Find(What:=SearchTarget, LookAt:= _
xlPart, SearchDirection:=xlNext, MatchCase:=False).Execute
ActiveCell.Select

End Sub
 
How can your rngrow use an activecell when it is no longer the active cell?
Keep digging.

--
Don Guillett
SalesAid Software
(e-mail address removed)
Jim Youlong said:
Don,

Thanks for the help.

I took your suggestions and produced the following code.
Unfortunately, it still generates an error. As for your question, how
do we know which cell we arrive at on Sheet(1)[now retitled "Data"], I
will try to explain as best I can. We start off by selecting a cell on
the "Data" sheet, which is used as an object in an entirely different
subroutine to take us to a second sheet. On this sheet, I would like
to give the user the option to select any value and search for it on
the first sheet ("Data") in a range around the original cell selected.
I wonder if that makes sense. Anyway, here is the code

Sub Test()

Dim SearchTarget As String
Dim RngTop, RngBot As Integer

SearchTarget = Selection 'on the second sheet

RngRow = Worksheets("Data").ActiveCell.Row 'original cell selected
before
'before arriving at the
'second sheet

'range demarcated by spaces in a specific column
RngBot = Range("I" & RngRow).End(xlDown).Row
RngTop = Range("I" & RngRow).End(xlUp).Row
Rng = Range("G" & RngTop, "G" & RngBot)
Rng.Find(What:=SearchTarget, LookAt:= _
xlPart, SearchDirection:=xlNext, MatchCase:=False).Execute
ActiveCell.Select

End Sub

"Don Guillett" <[email protected]> wrote in message
Looks over complicated. Simpler.

SearchTarget = Selection
Worksheets(1).Activate 'don't have to do that.
RngRow = activecell.Row

How do we know at what cell we will arrive when we get to sheet1?
Click to expand...
Click to expand...
 
Don,

As far as I know, each sheet maintains its own active cell. The code
is able to select the proper range on the "Data" sheet. And I can
write it to select a random cell within the range. However, when I do
a Find function, it errors. Any idea why?

Greg



Don Guillett said:
How can your rngrow use an activecell when it is no longer the active cell?
Keep digging.

--
Don Guillett
SalesAid Software
(e-mail address removed)
Jim Youlong said:
Don,

Thanks for the help.

I took your suggestions and produced the following code.
Unfortunately, it still generates an error. As for your question, how
do we know which cell we arrive at on Sheet(1)[now retitled "Data"], I
will try to explain as best I can. We start off by selecting a cell on
the "Data" sheet, which is used as an object in an entirely different
subroutine to take us to a second sheet. On this sheet, I would like
to give the user the option to select any value and search for it on
the first sheet ("Data") in a range around the original cell selected.
I wonder if that makes sense. Anyway, here is the code

Sub Test()

Dim SearchTarget As String
Dim RngTop, RngBot As Integer

SearchTarget = Selection 'on the second sheet

RngRow = Worksheets("Data").ActiveCell.Row 'original cell selected
before
'before arriving at the
'second sheet

'range demarcated by spaces in a specific column
RngBot = Range("I" & RngRow).End(xlDown).Row
RngTop = Range("I" & RngRow).End(xlUp).Row
Rng = Range("G" & RngTop, "G" & RngBot)
Rng.Find(What:=SearchTarget, LookAt:= _
xlPart, SearchDirection:=xlNext, MatchCase:=False).Execute
ActiveCell.Select

End Sub

"Don Guillett" <[email protected]> wrote in message
Looks over complicated. Simpler.

SearchTarget = Selection
Worksheets(1).Activate 'don't have to do that.
RngRow = activecell.Row

How do we know at what cell we will arrive when we get to sheet1?


--
Don Guillett
SalesAid Software
(e-mail address removed)
Hi.

I am trying to do a find in a range. Essentially, I want to find the
value of a cell on one page in a specific range on a second page. When
I incorporate a Range object (i.e. Rng) in to the code and I start
getting errors. Any assistance someone might give would be greatly
appreciated.

Here's a simplified version of the code.

Sub MainPageSearchRange()
Dim SearchTarget As String
Dim RngTop, RngBot As Integer
Dim SlctCll As String

SearchTarget = ActiveWindow.Selection.Cells.Value

Worksheets(1).Activate
Set InRng = ActiveCell
RngRow = InRng.Row
'range demarcated by spaces in a specific column
RngBot = Range("I" & RngRow).End(xlDown).Row
RngTop = Range("I" & RngRow).End(xlUp).Row
Rng = Range("G" & RngTop, "G" & RngBot)
Rng.Find(What:=SearchTarget, LookAt:= _
xlPart, SearchDirection:=xlNext, MatchCase:=False).Execute
ActiveCell.Select
End Sub

Thanks

Greg
Click to expand...
Click to expand...
Click to expand...
 
Send me a SMALL workbook to the address below along with a COMPLETE
explanation of what you are trying to do.

--
Don Guillett
SalesAid Software
(e-mail address removed)
Jim Youlong said:
Don,

As far as I know, each sheet maintains its own active cell. The code
is able to select the proper range on the "Data" sheet. And I can
write it to select a random cell within the range. However, when I do
a Find function, it errors. Any idea why?

Greg



"Don Guillett" <[email protected]> wrote in message
How can your rngrow use an activecell when it is no longer the active cell?
Keep digging.

--
Don Guillett
SalesAid Software
(e-mail address removed)
Jim Youlong said:
Don,

Thanks for the help.

I took your suggestions and produced the following code.
Unfortunately, it still generates an error. As for your question, how
do we know which cell we arrive at on Sheet(1)[now retitled "Data"], I
will try to explain as best I can. We start off by selecting a cell on
the "Data" sheet, which is used as an object in an entirely different
subroutine to take us to a second sheet. On this sheet, I would like
to give the user the option to select any value and search for it on
the first sheet ("Data") in a range around the original cell selected.
I wonder if that makes sense. Anyway, here is the code

Sub Test()

Dim SearchTarget As String
Dim RngTop, RngBot As Integer

SearchTarget = Selection 'on the second sheet

RngRow = Worksheets("Data").ActiveCell.Row 'original cell selected
before
'before arriving at the
'second sheet

'range demarcated by spaces in a specific column
RngBot = Range("I" & RngRow).End(xlDown).Row
RngTop = Range("I" & RngRow).End(xlUp).Row
Rng = Range("G" & RngTop, "G" & RngBot)
Rng.Find(What:=SearchTarget, LookAt:= _
xlPart, SearchDirection:=xlNext, MatchCase:=False).Execute
ActiveCell.Select

End Sub

"Don Guillett" <[email protected]> wrote in message
Click to expand...
Looks over complicated. Simpler.

SearchTarget = Selection
Worksheets(1).Activate 'don't have to do that.
RngRow = activecell.Row

How do we know at what cell we will arrive when we get to sheet1?


--
Don Guillett
SalesAid Software
(e-mail address removed)
Hi.

I am trying to do a find in a range. Essentially, I want to find the
value of a cell on one page in a specific range on a second page. When
I incorporate a Range object (i.e. Rng) in to the code and I start
getting errors. Any assistance someone might give would be greatly
appreciated.

Here's a simplified version of the code.

Sub MainPageSearchRange()
Dim SearchTarget As String
Dim RngTop, RngBot As Integer
Dim SlctCll As String

SearchTarget = ActiveWindow.Selection.Cells.Value

Worksheets(1).Activate
Set InRng = ActiveCell
RngRow = InRng.Row
'range demarcated by spaces in a specific column
RngBot = Range("I" & RngRow).End(xlDown).Row
RngTop = Range("I" & RngRow).End(xlUp).Row
Rng = Range("G" & RngTop, "G" & RngBot)
Rng.Find(What:=SearchTarget, LookAt:= _
xlPart, SearchDirection:=xlNext, MatchCase:=False).Execute
ActiveCell.Select
End Sub

Thanks

Greg
Click to expand...
Click to expand...
Click to expand...
 
Back
Top