Microsoft Office Forums

Go Back   Microsoft Office Forums > >

 
 
Thread Tools Display Modes
Prev Previous Post   Next Post Next
  #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, 13 views)
Reply With Quote
 

Tags
vba code



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 06:38 AM.


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