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

jonco wrote:

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

--

Dave Peterson

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


"jonco" wrote:

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