select cell based on two searches
 

I am trying to select a cell on another sheet as follows:

searche Sheet2 for the text located in Sheet1 A1.
(I can do this first step)

Now, I want to search only the column of the previous search result for text
located in Sheet1 A2.

I have used this code which I found in a previous post to do the first part
of the search.

x = ActiveCell
y = Sheets("monthly budget").Cells.Find(x).Address
Application.Goto Sheets("monthly budget").Range(y), Scroll:=True

I have also stumbled around with my limited knowledge and have accomplished
my desired result, but it is very messy and requires jumping back and forth
from sheet to sheet with copying and pasting. I know there has to be a better
way.

thanks in advance,

Melissa

select cell based on two searches
 

Sub FindTwice()
dim rng1 as Range, rng2 as range
Dim rng3 as Range, rng4 as Range
set rng1 = Worksheets("Sheet1").Range("A1")
set rng2 = Worksheets("Sheet1").Range("A2")
set rng3 = Worksheets("Sheet2").Cells.Fine(rng1.Value)
if not rng3 is nothing then
set rng4 = rng3.EntireColumn.Find(rng2.Value)
if not rng4 is nothing then
msgbox "found at " & rng4.Address(0,0,xlA1,true)
application.Goto rng4, True
else
msgbox rng2 & " was not found in column " & rng2.column
end if
else
msgbox rng1 & " was not found"
End if
End Sub

--
Regards,
Tom Ogilvy



"beauty_bobaloo" wrote in message
...
I am trying to select a cell on another sheet as follows:

searche Sheet2 for the text located in Sheet1 A1.
(I can do this first step)

Now, I want to search only the column of the previous search result for
text
located in Sheet1 A2.

I have used this code which I found in a previous post to do the first
part
of the search.

x = ActiveCell
y = Sheets("monthly budget").Cells.Find(x).Address
Application.Goto Sheets("monthly budget").Range(y), Scroll:=True

I have also stumbled around with my limited knowledge and have
accomplished
my desired result, but it is very messy and requires jumping back and
forth
from sheet to sheet with copying and pasting. I know there has to be a
better
way.

thanks in advance,

Melissa

select cell based on two searches
 

Thankyou Tom, that worked great! I think I need to try and track down A VB
book For Excel.
I really want to learn. I feel like I am always relying on someone elses
knowledge.

I am very greatful for your help.

Melissa.

"Tom Ogilvy" wrote:

Sub FindTwice()
dim rng1 as Range, rng2 as range
Dim rng3 as Range, rng4 as Range
set rng1 = Worksheets("Sheet1").Range("A1")
set rng2 = Worksheets("Sheet1").Range("A2")
set rng3 = Worksheets("Sheet2").Cells.Fine(rng1.Value)
if not rng3 is nothing then
set rng4 = rng3.EntireColumn.Find(rng2.Value)
if not rng4 is nothing then
msgbox "found at " & rng4.Address(0,0,xlA1,true)
application.Goto rng4, True
else
msgbox rng2 & " was not found in column " & rng2.column
end if
else
msgbox rng1 & " was not found"
End if
End Sub

--
Regards,
Tom Ogilvy



"beauty_bobaloo" wrote in message
...
I am trying to select a cell on another sheet as follows:

searche Sheet2 for the text located in Sheet1 A1.
(I can do this first step)

Now, I want to search only the column of the previous search result for
text
located in Sheet1 A2.

I have used this code which I found in a previous post to do the first
part
of the search.

x = ActiveCell
y = Sheets("monthly budget").Cells.Find(x).Address
Application.Goto Sheets("monthly budget").Range(y), Scroll:=True

I have also stumbled around with my limited knowledge and have
accomplished
my desired result, but it is very messy and requires jumping back and
forth
from sheet to sheet with copying and pasting. I know there has to be a
better
way.

thanks in advance,

Melissa