![]() |
#1
|
|||
|
|||
![]()
I have a table with 4 columns and what I was trying to do is:
1. Make a user input box 2. Search in Column "A" for user input 3. If item is found several rows down bring that row to top of page. The idea here is to bring up data values that might be several hundred rows down and bring that data to the top of the page. I'm sure there must be a way to do this but I don't have a clue how to make it work. I attached a sample file to use if need be. Thanks in advance everyone. ![]() Last edited by Auto; 03-08-2016 at 01:06 AM. |
#2
|
|||
|
|||
![]()
This will do what you want. It will put the first row holding the text you've entered in row 2 at the top of the table
Code:
Option Explicit Sub FindSomething() Dim Ws1 As Worksheet Dim What2Find As String, sFalse As String Dim TopRow As Range Dim FoundRow As Long Set Ws1 = ThisWorkbook.Sheets(1) sFalse = "False" On Error Resume Next Application.DisplayAlerts = False What2Find = Application.InputBox("Enter Here", "Find Something") ' Text Input ' Test if input box is empty or cancel is pressed If What2Find = vbNullString Or What2Find = sFalse Then GoTo ExitOut FoundRow = Ws1.Columns(1).Find(what:=What2Find, LookIn:=xlValues, _ lookat:=xlPart, searchorder:=xlByRows, SearchDirection:=xlNext, _ MatchCase:=False, searchformat:=False).Row If FoundRow = 0 Then GoTo NothingFound ' Copy the data from the row it was found on to row 2 at the top of the table Ws1.Range(Ws1.Cells(FoundRow, 1), Ws1.Cells(FoundRow, 4)).Copy Ws1.Range(Ws1.Cells(2, 1), Ws1.Cells(2, 4)) GoTo ExitOut NothingFound: MsgBox " Not Found" Application.DisplayAlerts = True On Error GoTo 0 Exit Sub ExitOut: Application.DisplayAlerts = True On Error GoTo 0 Exit Sub End Sub |
#4
|
|||
|
|||
![]()
No problem, glad it worked for you
|
![]() |
Tags |
vba code |
Thread Tools | |
Display Modes | |
|
![]() |
||||
Thread | Thread Starter | Forum | Replies | Last Post |
![]() |
paik1002 | Word VBA | 1 | 02-18-2016 03:51 PM |
Same doc, different computers, page numbers don't match | msofword | Word | 4 | 10-30-2015 02:41 PM |
![]() |
Duncanbh | Excel | 2 | 09-14-2015 01:42 PM |
![]() |
michaelbriordan | Word | 3 | 06-17-2015 10:12 AM |
VBA find keyword and move to location then add symbol | Jmanville | Word VBA | 3 | 10-22-2014 01:45 AM |