Macro to jump from the find box to found item

[Todd]

Is it possible to program a macro that would allow me to
jump from the find box to the cell of the found item after
I do a find and even better would be to jump to a location
that would be two columns to the left of the found value.

For example I type "3876" in the find box so then I want
to jump to a position on the sheet 2 columns to the left
of the 3876.

This is for a highly repeatable data entry task that will
be ongoing.

Thanks Todd

 

[todd frisch]

I forgot the email address her it is

(e-mail address removed)

 

[Don Guillett]

try this
Sub gotoselection()
x = Cells.Find(InputBox("Enter number to find")).Address
Application.Goto reference:=Range(x).Offset(, 2)
End Sub

 

[Guest]

Thanks but instead of jumping to the 2 left of the item
positon I need it jumps to the right of the found item by
2 spaces

Also when I run it it pops up a dialog box - I need it to
read the number in the find dialog box then jump directly
to the found item location - well 2 columns to the left of
the found item on the sheet.

Thanks

 

[Don Guillett]

What I sent precludes the need for the find dialog box.
If you want it to move 2 cells to the LEFT, then change the ,2 to ,-2

Maybe I didn't understand your needs. Perhaps more detail would be helpful.

 

[Guest]

WOW - WAY COOL THANKS MUCH - I EVEN CAN HIT FIND BUTTON
AND THE LAST NUMBER POPS UP IN IT WHEN I NEED TO DO A FIND
ALL

GREATLY APPRECIATE THE TIME

 

[Don Guillett]

Glad to help. But you don't have to SHOUT about it.
Again, maybe your chores could be greatly shortened with an explanation of
what you are trying to do.

 

[todd]

thank you it works great - i just did not understand -
having posting trouble

 

[KAUG]

Don,
this is exactly what i was looking for today ! thank you -
one question, how can i add a FIND ANOTHER or FIND NEXT
selection, so if the person types in a number and there
are multiples in the column they can continue looking from
there on.
thank you again!

 

[Don Guillett]

It was right there, staring you in the face when you went to vbe help index
and typed in FIND..
Modify to suit
With Worksheets(1).Range("a1:a500")
Set c = .Find(2, lookin:=xlValues)
If Not c Is Nothing Then
firstAddress = c.Address
Do
c.Value = 5
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address <> firstAddress
End If
End With

 

[todd]

Dare I ask is there a way to get the active cell that I
goto with the use of your incredible macro that will
position the active cell approximately mid screen or
atleast at almost mid screen so I can read what is above
and below the active looked up cell for a few rows.

thanks

 

[steve]

Todd,

Try this:

Application.GoTo Reference:=Range(ActiveCell.Offset(-5, -6).Address),
Scroll:=True

(correct for word wrap)

Change the -5 and the -6 to get the centering you would like.

 

[todd]

Thank you - I have to confess I have been away from vba
for awhile and I am not to advanced to begin with - may I
ask how to work that into the code below

Thank Todd

Sub gotoselection()
x = Cells.Find(InputBox("Enter number to find")).Address
Application.Goto reference:=Range(x).Offset(, -2)
End Sub

 

[steve]

Todd,

This placed the offset cell in the upper left corner of the screen.
The code opens an input box for the value to find and than positions the
screen. Set -6 to the number of rows above, and -2 to the number of
columns to the left. It also avoids an errors if the offset is off the
screen.

Dim x As String
x = Cells.Find(InputBox("Enter number to find")).Address
On Error Resume Next
Application.Goto reference:=Range(x).Offset(-6, -2), Scroll:=True
On Error GoTo 0

 

[pfsardella]

Chip Pearson has devloped a routine for this. The coding is somewhat
complex, but the application is easy. He has good instructions.

http://www.cpearson.com/excel/zoom.htm#center

HTH
Paul