Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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/ |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Cursor jumping to next cell | Excel Discussion (Misc queries) | |||
JUMPING TO THE NEAREST FORMULA CONTAINING CELL | Excel Discussion (Misc queries) | |||
Jumping reference cell in dependent cell formula | Excel Worksheet Functions | |||
Jumping to a certain cell | Excel Discussion (Misc queries) | |||
Jumping to the next cell automaticly | Excel Discussion (Misc queries) |