View Single Post
 
Old 03-08-2016, 12:29 AM
Philb1 Philb1 is offline Windows 10 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