Finding a cell

  • Thread starter Thread starter Alan
  • Start date Start date
A

Alan

Hi,

Firstly, I am no expert at VBA!

Application.Goto Range([A1]), scroll:=True

will scroll to the cell specified in A1.

Is there a way to scroll by way of the value in a cell. That is to say, if
the value VR128 were in cell AQ552, is there a way to scroll to that cell by
entering something like (pigeon code)

Application.Goto "VR128",scroll:=True

Any help gratefully recieved,

Regards,

Alan.
 
Sub test()
Dim str As String
' in A1 c10 for example
str = Range("A1")
Application.Goto Range(str)
End Sub

Do you mean this Alan
VR128 don't exist
 
Hi Alan

Application.Goto Range(Range("AQ552").Value), scroll:=True

will do the job.

--
Best Regards
Leo Heuser
MVP Excel

Followup to newsgroup only please.
 
Ron and Leo,
Thanks very much indeed for the replies but I didn't
explain myself properly I don't think. Its hard to do so sometimes when you
are unsure even if its possible and learning,
What I meant was :- Is it possible to find a cell by the
value it contains, not by the reference? ie if any cell in the sheet
contains for example "Cat and Dog" or "1234567" or any other value (these
would be unique), could that cell be found in the same way that Edit > Find
would find it, except by using code, and if so to scroll to it? That is to
say, I don't know which cell contains the value, I only know what the value
is, and that is the cell I want to scroll to.
Edit > Find will do it I know, but if possible I'd like
to enter the value of the target cell value in say A1, and have a macro
scroll to it via Range([A1])
Thanks Again,
Alan.
(If I'm not making sense, I'll go away and be quiet)

Leo Heuser said:
Hi Alan

Application.Goto Range(Range("AQ552").Value), scroll:=True

will do the job.

--
Best Regards
Leo Heuser
MVP Excel

Followup to newsgroup only please.

Alan said:
Hi,

Firstly, I am no expert at VBA!

Application.Goto Range([A1]), scroll:=True

will scroll to the cell specified in A1.

Is there a way to scroll by way of the value in a cell. That is to say, if
the value VR128 were in cell AQ552, is there a way to scroll to that
Click to expand...
cell
by
entering something like (pigeon code)

Application.Goto "VR128",scroll:=True

Any help gratefully recieved,

Regards,

Alan.
Click to expand...
Click to expand...
 
Hi Leo,
This code does exactly what I wanted it to do, thanks a million
for sharing your expertise with me, not to mention your patience!
Best Wishes,
Alan.

Leo Heuser said:
You are making perfect sense, Alan :-)

Try something along these lines:
(It won't work for times)
Try experimenting with LookIn:=xlValues


Sub FindItem()
'Leo Heuser, 22-7-2003
Dim FindValue As Variant
Dim Found As Range
Dim SearchRange As Range
Dim SourceCell As Range

Set SourceCell = ActiveSheet.Range("a1")

FindValue = SourceCell.Value

Set SearchRange = ActiveSheet.Cells

Set Found = SearchRange.Find(FindValue, _
LookIn:=xlFormulas, _
LookAt:=xlPart, _
MatchCase:=False)
If Not Found Is Nothing Then
If Found.Address = SourceCell.Address Then
Set Found = SearchRange.FindNext(Found)
End If

Application.Goto Found, scroll:=True
End If

End Sub


--
Best Regards
Leo Heuser
MVP Excel

Followup to newsgroup only please.

Alan said:
Ron and Leo,
Thanks very much indeed for the replies but I didn't
explain myself properly I don't think. Its hard to do so sometimes when you
are unsure even if its possible and learning,
What I meant was :- Is it possible to find a cell by the
value it contains, not by the reference? ie if any cell in the sheet
contains for example "Cat and Dog" or "1234567" or any other value (these
would be unique), could that cell be found in the same way that Edit > Find
would find it, except by using code, and if so to scroll to it? That is to
say, I don't know which cell contains the value, I only know what the value
is, and that is the cell I want to scroll to.
Edit > Find will do it I know, but if possible I'd like
to enter the value of the target cell value in say A1, and have a macro
scroll to it via Range([A1])
Thanks Again,
Alan.
(If I'm not making sense, I'll go away and be quiet)

Leo Heuser said:
Hi Alan

Application.Goto Range(Range("AQ552").Value), scroll:=True

will do the job.

--
Best Regards
Leo Heuser
MVP Excel

Followup to newsgroup only please.

"Alan" <[email protected]> skrev i en meddelelse
Hi,

Firstly, I am no expert at VBA!

Application.Goto Range([A1]), scroll:=True

will scroll to the cell specified in A1.

Is there a way to scroll by way of the value in a cell. That is to
Click to expand...
say,
if
the value VR128 were in cell AQ552, is there a way to scroll to that cell
by
entering something like (pigeon code)

Application.Goto "VR128",scroll:=True

Any help gratefully recieved,

Regards,

Alan.
Click to expand...
Click to expand...
Click to expand...
 
You're welcome, Alan.
Glad to be able to help.
Thanks for the feedback It's always appreciated..
 
Back
Top