jumping to a cell which matches user specified text
 

hi, i have a list of parts in a sheet.

i want the user to be able to enter a part number (this will be unique)
then excell to move to the row which holds that part number.

basically what the built in goto function does in the edit menu.

basically...

user runs macro...

input box enter the part number.....

searches for that part number......

moves to the row which holds that part number.

thanks in advance

matthew


------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~View and post usenet messages directly from http://www.ExcelForum.com/

~~Now Available: Financial Statements.xls, a step by step guide to creating financial statements

jumping to a cell which matches user specified text
 

Try this...


wscount = Workbooks("workbookName.xls").Worksheets.Count
line1: item = InputBox("Input what you would like to search for." )

'designate the column number and column header name to search in
col = 4
ColName = "Header_Name"


With Workbooks("workbookName.xls").Worksheets("sheet1") .Range("A1")
Numrows = Range(.Offset(1, col), .End(xlDown)).Rows.Count
Range(.Offset(1, col), .Offset(1, col).End(xlDown)).Name =
ColName
End With

Worksheets("sheet1").Activate
With Range(ColName)
Set c = .Find(item, LookIn:=xlValues)
If Not c Is Nothing Then
firstaddress = c.address
Do
'the next two lines identify the item by bolding and coloring each
instance found
c.Font.Bold = True
c.Font.ColorIndex = 3
Count = Count + 1
'this puts the address of each instance of the search item into an
array
address(Count) = c.address
Range(c.address).select
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.address <
firstaddress
End If
End With
'if no instances of item are found go back to the input box
If Count = 0 Then GoTo line1


------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~View and post usenet messages directly from http://www.ExcelForum.com/

~~Now Available: Financial Statements.xls, a step by step guide to creating financial statements

jumping to a cell which matches user specified text
 

that didnt work, it gave me an error with the line

address(Count) = c.address

also if address is an array, shouldnt it be declared at the top? cause
when i changed something it said subscript out of range...

thanks
matthew


---
~~ Message posted from http://www.ExcelForum.com/

jumping to a cell which matches user specified text
 

Wouldn't it be easier just using edit|Find or just ctrl-F?

If you really wanted a macro:

Option Explicit
Sub testme02()

Dim myWhatToFind As Variant
Dim FoundCell As Range

myWhatToFind = InputBox(Prompt:="Find What?", Title:="PartNumber?")

If myWhatToFind = False Then
Exit Sub 'user hit cancel
End If

With ActiveSheet.Range("a:a")

Set FoundCell = .Cells.Find(What:=myWhatToFind, _
After:=.Cells(.Cells.Count), LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)

If FoundCell Is Nothing Then
Beep
Else
Application.Goto FoundCell, scroll:=True
End If
End With

End Sub



bikestlr wrote:

hi, i have a list of parts in a sheet.

i want the user to be able to enter a part number (this will be unique)
then excell to move to the row which holds that part number.

basically what the built in goto function does in the edit menu.

basically...

user runs macro...

input box enter the part number.....

searches for that part number......

moves to the row which holds that part number.

thanks in advance

matthew

------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~View and post usenet messages directly from http://www.ExcelForum.com/

~~Now Available: Financial Statements.xls, a step by step guide to creating financial statements

--

Dave Peterson