Macro to jump from the find box to found item

  • Thread starter Thread starter Todd
  • Start date Start date
T

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
 
try this
Sub gotoselection()
x = Cells.Find(InputBox("Enter number to find")).Address
Application.Goto reference:=Range(x).Offset(, 2)
End Sub
 
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
 
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.
 
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
 
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.
 
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!
 
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
 
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
 
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.
 
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
 
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
 
Back
Top