Macro to find last occurance
 

This is the code I have so far; however instead of finding the first
occurrence of what was entered into the input box, I need the last occurrence
in column C. Can anyone help?

Sub Insertinfo()
Dim FindWhat As String
Dim FoundCell As Range

FindWhat = InputBox("Find What", "Find")
If StrPtr(FindWhat) = 0 Then
Exit Sub
End If
On Error Resume Next
Set FoundCell = Range("c:c").Find(What:=FindWhat, _
LookAt:=xlWhole, LookIn:=xlValues)
If FoundCell Is Nothing Then
MsgBox "Not Found"
Else
FoundCell.Activate
With ActiveCell.EntireRow
.Copy
.Insert Shift:=xlDown
End With
End If
Application.CutCopyMode = False
End Sub

Thanks,
Holly

Macro to find last occurance
 

Try this

If you have more then one occurrence of the value this will
select the last occurrence in column C.

Sub Find_Last()
Dim FindString As String
Dim rng As Range
FindString = InputBox("Enter a Search value")
If Trim(FindString) < "" Then
With Sheets("Sheet1").Range("C:C")
Set rng = .Find(What:=FindString, _
After:=.Cells(1), _
LookIn:=xlValues, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False)
If Not rng Is Nothing Then
Application.Goto rng, True
Else
MsgBox "Nothing found"
End If
End With
End If
End Sub


--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"hnyb1" wrote in message ...
This is the code I have so far; however instead of finding the first
occurrence of what was entered into the input box, I need the last occurrence
in column C. Can anyone help?

Sub Insertinfo()
Dim FindWhat As String
Dim FoundCell As Range

FindWhat = InputBox("Find What", "Find")
If StrPtr(FindWhat) = 0 Then
Exit Sub
End If
On Error Resume Next
Set FoundCell = Range("c:c").Find(What:=FindWhat, _
LookAt:=xlWhole, LookIn:=xlValues)
If FoundCell Is Nothing Then
MsgBox "Not Found"
Else
FoundCell.Activate
With ActiveCell.EntireRow
.Copy
.Insert Shift:=xlDown
End With
End If
Application.CutCopyMode = False
End Sub

Thanks,
Holly

Macro to find last occurance
 

Thank you much, especially for the quick response! It worked beautifully.

"Ron de Bruin" wrote:

Try this

If you have more then one occurrence of the value this will
select the last occurrence in column C.

Sub Find_Last()
Dim FindString As String
Dim rng As Range
FindString = InputBox("Enter a Search value")
If Trim(FindString) < "" Then
With Sheets("Sheet1").Range("C:C")
Set rng = .Find(What:=FindString, _
After:=.Cells(1), _
LookIn:=xlValues, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False)
If Not rng Is Nothing Then
Application.Goto rng, True
Else
MsgBox "Nothing found"
End If
End With
End If
End Sub


--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"hnyb1" wrote in message ...
This is the code I have so far; however instead of finding the first
occurrence of what was entered into the input box, I need the last occurrence
in column C. Can anyone help?

Sub Insertinfo()
Dim FindWhat As String
Dim FoundCell As Range

FindWhat = InputBox("Find What", "Find")
If StrPtr(FindWhat) = 0 Then
Exit Sub
End If
On Error Resume Next
Set FoundCell = Range("c:c").Find(What:=FindWhat, _
LookAt:=xlWhole, LookIn:=xlValues)
If FoundCell Is Nothing Then
MsgBox "Not Found"
Else
FoundCell.Activate
With ActiveCell.EntireRow
.Copy
.Insert Shift:=xlDown
End With
End If
Application.CutCopyMode = False
End Sub

Thanks,
Holly