Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 03-07-2016, 12:25 PM
Auto Auto is offline Find a match and move row to top of page Windows 8 Find a match and move row to top of page Office 2007
Novice
Find a match and move row to top of page
 
Join Date: Mar 2016
Posts: 9
Auto is on a distinguished road
Default Find a match and move row to top of page


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.
Attached Files
File Type: xlsx sample.xlsx (19.9 KB, 10 views)

Last edited by Auto; 03-08-2016 at 01:06 AM.
Reply With Quote
  #2  
Old 03-08-2016, 12:29 AM
Philb1 Philb1 is offline Find a match and move row to top of page Windows 10 Find a match and move row to top of page Office 2010 32bit
Advanced Beginner
 
Join Date: Feb 2016
Location: Auckland
Posts: 43
Philb1 is on a distinguished road
Default

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
Attached Files
File Type: xlsm prototype-1.xlsm (35.2 KB, 11 views)
Reply With Quote
  #3  
Old 03-08-2016, 01:10 AM
Auto Auto is offline Find a match and move row to top of page Windows 8 Find a match and move row to top of page Office 2007
Novice
Find a match and move row to top of page
 
Join Date: Mar 2016
Posts: 9
Auto is on a distinguished road
Default

@Philb1

OMG you did it. Wow thanks a lot for taking the time to do this I really appreciate what you did here because it works great, exactly what I was looking for. YOU ROCK
Reply With Quote
  #4  
Old 03-08-2016, 01:17 AM
Philb1 Philb1 is offline Find a match and move row to top of page Windows 10 Find a match and move row to top of page Office 2010 32bit
Advanced Beginner
 
Join Date: Feb 2016
Location: Auckland
Posts: 43
Philb1 is on a distinguished road
Default

No problem, glad it worked for you
Reply With Quote
Reply

Tags
vba code

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Find a match and move row to top of page Find a fuzzy match of a sentence. 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
Find a match and move row to top of page Auto text find and move Duncanbh Excel 2 09-14-2015 01:42 PM
Find a match and move row to top of page Word Find won't move out of footnote 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

Other Forums: Access Forums

All times are GMT -7. The time now is 10:56 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft