jump to cell

[greg7468]

Hello all,
I have this macro, when a number is placed in B1 finds tha
number in column A and jumps to it.
It works fine when column A is all numbers but doesn't seem t
work if I put text and numbers in.
I would like it to do the same when say looking for text an
numbers so I could put X100 in B1 and it still jump to the same valu
in column A.
Could someone please help as to whether this is possible with
bit of tweaking.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address <> "$B$1" Then Exit Sub
Set x = Columns(1).Find(Target.Value)
Application.Goto Range(x.Address), Scroll:=True
End Su

 

[Dave Peterson]

Your code worked ok for me when I tested it.

But .find is one of those things that remember the parameters from the last time
you used it. So if you looked in comments, you'd still be looking in comments
with your code. You can specify all the parms and take the worry out of it:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

Dim FoundCell As Range

If Target.Address <> "$B$1" Then Exit Sub

With Me.Columns(1)
Set FoundCell = .Cells.Find(what:=Target.Value, _
after:=.Cells(.Cells.Count), _
LookIn:=xlValues, lookat:=xlPart, _
searchorder:=xlByRows, searchdirection:=xlNext, _
MatchCase:=False)
End With

If FoundCell Is Nothing Then
MsgBox "not found in column A"
Else
Application.Goto FoundCell, Scroll:=True
End If

End Sub

I used xlpart--not xlwhole. And matchcase:=false (not true).

Modify them the way you want.

 

[greg7468]

Thanks for the reply Dave it was actually my mistake I had the targe
address set to A15,(I copied the code from another sheet) so it did no
work, as you said it does work properly with text if you use i
correctly