Macro Code minor alteration please.
 

I was shown a code and I think it was misunderstood.
Not sure how to slighty modify the part of the code to do a search.

It was suppose to search for the value in Sheet1 A1 ( fixed location)
in Sheet 2 "anywhere"

So if the macro is running and it reaches the point to find the value
of Sheet 1 A1, then Select Sheet 2, find the same value anywhere in
Sheet 2, and then select that cell.
From there I have the rest covered after the End If

The code I was shown looks like this:
-----------------------------------------------

Sub Find_First()
Dim FindString As String
Dim Rng As Range
FindString = InputBox("Enter a Search value")

If Trim(FindString) < "" Then
Set Rng = Range("A:A").find(What:=FindString, _
After:=Range("A" & Rows.Count), _
LookIn:=xlFormulas, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)
If Not Rng Is Nothing Then
Application.Goto Rng, True
Else
MsgBox "Nothing found"
End If
End If
End Sub ( or continue with the rest of the macro)

But the misunderstanding was where it shows;

FindString = InputBox("Enter a Search value")

This means it has to be typed in. There is no need to type in the
value, it is always in the same location.
It changes value, is when the macro was activated before anyway.

How can I then make that piece of macro code, go to Sheet 2 and locate
the same value as in Sheet 1 A1 without requireinf to type in a value
?

Much appreciated


--
RPTZ
------------------------------------------------------------------------
RPTZ's Profile: http://www.excelforum.com/member.php...o&userid=24896
View this thread: http://www.excelforum.com/showthread...hreadid=384329

change
FindString = InputBox("Enter a Search value")
to
FindString = Sheets("A1").range("A1").value

"RPTZ" wrote:


I was shown a code and I think it was misunderstood.
Not sure how to slighty modify the part of the code to do a search.

It was suppose to search for the value in Sheet1 A1 ( fixed location)
in Sheet 2 "anywhere"

So if the macro is running and it reaches the point to find the value
of Sheet 1 A1, then Select Sheet 2, find the same value anywhere in
Sheet 2, and then select that cell.
From there I have the rest covered after the End If

The code I was shown looks like this:
-----------------------------------------------

Sub Find_First()
Dim FindString As String
Dim Rng As Range
FindString = InputBox("Enter a Search value")

If Trim(FindString) < "" Then
Set Rng = Range("A:A").find(What:=FindString, _
After:=Range("A" & Rows.Count), _
LookIn:=xlFormulas, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)
If Not Rng Is Nothing Then
Application.Goto Rng, True
Else
MsgBox "Nothing found"
End If
End If
End Sub ( or continue with the rest of the macro)

But the misunderstanding was where it shows;

FindString = InputBox("Enter a Search value")

This means it has to be typed in. There is no need to type in the
value, it is always in the same location.
It changes value, is when the macro was activated before anyway.

How can I then make that piece of macro code, go to Sheet 2 and locate
the same value as in Sheet 1 A1 without requireinf to type in a value
?

Much appreciated


--
RPTZ
------------------------------------------------------------------------
RPTZ's Profile: http://www.excelforum.com/member.php...o&userid=24896
View this thread: http://www.excelforum.com/showthread...hreadid=384329

Thanks, it did not seem to work either.

I get a error/debug alert saying:

"Subscripit out of range"

It has to be whatever the value is in "Sheet 1. A1"
then to locate that same value in Sheet 2. and go to the Cell location
in Sheet 2

Once it finds the same value in any cell range within Sheet 2, it
continues
with;
Selection.Offset(1, 0).Select '*Down*
Selection.Copy
Etc Etc,

It will help alot with accuracy if the values do not have to be typed
in.

Much appreciated.


--
RPTZ
------------------------------------------------------------------------
RPTZ's Profile: http://www.excelforum.com/member.php...o&userid=24896
View this thread: http://www.excelforum.com/showthread...hreadid=384329

Bob probably meant FindString = Sheets("Sheet1").range("A1").value


--
HTH

Bob Phillips

"RPTZ" wrote in message
...

Thanks, it did not seem to work either.

I get a error/debug alert saying:

"Subscripit out of range"

It has to be whatever the value is in "Sheet 1. A1"
then to locate that same value in Sheet 2. and go to the Cell location
in Sheet 2

Once it finds the same value in any cell range within Sheet 2, it
continues
with;
Selection.Offset(1, 0).Select '*Down*
Selection.Copy
Etc Etc,

It will help alot with accuracy if the values do not have to be typed
in.

Much appreciated.


--
RPTZ
------------------------------------------------------------------------
RPTZ's Profile:
http://www.excelforum.com/member.php...o&userid=24896
View this thread: http://www.excelforum.com/showthread...hreadid=384329

No, tried a few variations, not working.

I think it's best if anyone has a go, to just open a Workbook, 2
sheets as I have described in the example and try, rather than what
seems to be a guess.

Thanks


--
RPTZ
------------------------------------------------------------------------
RPTZ's Profile: http://www.excelforum.com/member.php...o&userid=24896
View this thread: http://www.excelforum.com/showthread...hreadid=384329

I noticed in your previous post you referred to Sheet1 as "Sheet1" and "Sheet
1"

Nothing wrong with Bob's code. I think you just need to decide on what the
sheet is named.

"RPTZ" wrote:


No, tried a few variations, not working.

I think it's best if anyone has a go, to just open a Workbook, 2
sheets as I have described in the example and try, rather than what
seems to be a guess.

Thanks


--
RPTZ
------------------------------------------------------------------------
RPTZ's Profile: http://www.excelforum.com/member.php...o&userid=24896
View this thread: http://www.excelforum.com/showthread...hreadid=384329

My last post got wrapped in a bad spot. The second worksheet name is
supposed to be

"Sheet 1"



"RPTZ" wrote:


No, tried a few variations, not working.

I think it's best if anyone has a go, to just open a Workbook, 2
sheets as I have described in the example and try, rather than what
seems to be a guess.

Thanks


--
RPTZ
------------------------------------------------------------------------
RPTZ's Profile: http://www.excelforum.com/member.php...o&userid=24896
View this thread: http://www.excelforum.com/showthread...hreadid=384329