Thread: 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

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.

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


--
HTH

Bob Phillips

"RPTZ" <RPTZ.1ro9en_1120525530.4419@excelforum-nospam.com> wrote in message
news:RPTZ.1ro9en_1120525530.4419@excelforum-nospam.com...
>
> 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

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
>
>