Help in highlighting and displaying found data.

  • Thread starter Thread starter jonco
  • Start date Start date
J

jonco

I need to modify a macro. This macro finds the first instance of a name
and then displays a dialog box telling the user that it has found the name
and it's row location. I need the macro to Select the row that it has found
(so the user can see it on the screen.) or at least scroll so it is visible.
It would be good to highlight it in some manner.

Here is the macro as it is now:

'Sub FindPastDue()

' FindPastDue Macro

Lastrow = Cells(Rows.Count, "A").End(xlUp).Row

Set namerange = Range(Cells(1, "A"), Cells(Lastrow, "A"))

SearchName = InputBox("Enter Name to Find: ")

Set C = namerange.Find(SearchName, LookIn:=xlValues)

If Not C Is Nothing Then

MsgBox ("Found name: " & c & " on line " & CStr(c.Row))

Else
MsgBox ("Did not find: " & SearchName)
End If


'
End Sub
 
How about:

Option Explicit
Sub FindPastDue()

Dim LastRow As Long
Dim wks As Worksheet
Dim NameRange As Range
Dim FoundCell As Range
Dim SearchName As String

Set wks = ActiveSheet

With wks
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
'just another way
Set NameRange = .Range("A1:A" & LastRow)
End With

SearchName = InputBox("Enter Name to Find: ")

With NameRange
Set FoundCell = .Cells.Find(what:=SearchName, _
LookAt:=xlWhole, _
after:=.Cells(.Cells.Count), _
LookIn:=xlValues, _
searchorder:=xlByRows, _
searchdirection:=xlNext, _
MatchCase:=False)
End With

If FoundCell Is Nothing Then
MsgBox "Did not find: " & SearchName
Else
MsgBox "Found Name: " & SearchName & " on line: " & FoundCell.Row
Application.Goto FoundCell, scroll:=True
End If

End Sub
 
Try this:

'Sub FindPastDue()

' FindPastDue Macro

Lastrow = Cells(Rows.Count, "A").End(xlUp).Row

Set namerange = Range(Cells(1, "A"), Cells(Lastrow, "A"))

SearchName = InputBox("Enter Name to Find: ")

Set C = namerange.Find(SearchName, LookIn:=xlValues)

If Not C Is Nothing Then

MsgBox ("Found name: " & c & " on line " & CStr(c.Row))
ActiveWindow.ScrollColumn = 1
ActiveWindow.ScrollRow = Cstr(C.Row)

Else
MsgBox ("Did not find: " & SearchName)
End If


'
End Sub
 
Back
Top