Thanks to Jim Thomlinson for the following code to move rows to another
worksheet.

This works perfectly where the value in column AV is text however what I
would like is for this column to be a formulae. This formulae is calculated
on whether the case is closed and on date (giving "current"/"non current"
result). The code doesn't seem to recognise the results if there is formulae
in column AV. Please help.

Sub MoveStuff()
Dim rngToSearch As Range
Dim rngFound As Range
Dim rngFoundAll As Range
Dim rngPaste As Range
Dim strFirstAddress As String

Set rngPaste = Sheets("_Non Current").Cells(Rows.Count, _
"A").End(xlUp).Offset(1, 0)
Set rngToSearch = ActiveSheet.Columns("AV")
Set rngFound = rngToSearch.Find(What:="closed", _
LookIn:=xlFormulas, _
LookAt:=xlWhole, _
MatchCase:=False)
If rngFound Is Nothing Then
MsgBox "There are no items to move."
Else
Set rngFoundAll = rngFound
strFirstAddress = rngFound.Address
Do
Set rngFoundAll = Union(rngFound, rngFoundAll)
Set rngFound = rngToSearch.FindNext(rngFound)
Loop Until rngFound.Address = strFirstAddress

rngFoundAll.EntireRow.Copy Destination:=rngPaste
rngFoundAll.EntireRow.Delete
End If
End Sub

Hi Janelle

Are you saying the result of the formula in AV gives either "current" or
"non current" as its result?
If so, then just amend 1 line of Jim's code from
Set rngFound = rngToSearch.Find(What:="closed", _
to
Set rngFound = rngToSearch.Find(What:="non current", _

--
Regards
Roger Govier

"Janelle S" wrote in message
...
Thanks to Jim Thomlinson for the following code to move rows to another
worksheet.

This works perfectly where the value in column AV is text however what I
would like is for this column to be a formulae. This formulae is
calculated
on whether the case is closed and on date (giving "current"/"non current"
result). The code doesn't seem to recognise the results if there is
formulae
in column AV. Please help.

Sub MoveStuff()
Dim rngToSearch As Range
Dim rngFound As Range
Dim rngFoundAll As Range
Dim rngPaste As Range
Dim strFirstAddress As String

Set rngPaste = Sheets("_Non Current").Cells(Rows.Count, _
"A").End(xlUp).Offset(1, 0)
Set rngToSearch = ActiveSheet.Columns("AV")
Set rngFound = rngToSearch.Find(What:="closed", _
LookIn:=xlFormulas, _
LookAt:=xlWhole, _
MatchCase:=False)
If rngFound Is Nothing Then
MsgBox "There are no items to move."
Else
Set rngFoundAll = rngFound
strFirstAddress = rngFound.Address
Do
Set rngFoundAll = Union(rngFound, rngFoundAll)
Set rngFound = rngToSearch.FindNext(rngFound)
Loop Until rngFound.Address = strFirstAddress

rngFoundAll.EntireRow.Copy Destination:=rngPaste
rngFoundAll.EntireRow.Delete
End If
End Sub

Thanks Roger

I changed the variable to read "non current" as suggested but I still get
the pop up msg ""There are no items to move." Any other suggestions?

"Roger Govier" wrote:

Hi Janelle

Are you saying the result of the formula in AV gives either "current" or
"non current" as its result?
If so, then just amend 1 line of Jim's code from
Set rngFound = rngToSearch.Find(What:="closed", _
to
Set rngFound = rngToSearch.Find(What:="non current", _

--
Regards
Roger Govier

"Janelle S" wrote in message
...
Thanks to Jim Thomlinson for the following code to move rows to another
worksheet.

This works perfectly where the value in column AV is text however what I
would like is for this column to be a formulae. This formulae is
calculated
on whether the case is closed and on date (giving "current"/"non current"
result). The code doesn't seem to recognise the results if there is
formulae
in column AV. Please help.

Sub MoveStuff()
Dim rngToSearch As Range
Dim rngFound As Range
Dim rngFoundAll As Range
Dim rngPaste As Range
Dim strFirstAddress As String

Set rngPaste = Sheets("_Non Current").Cells(Rows.Count, _
"A").End(xlUp).Offset(1, 0)
Set rngToSearch = ActiveSheet.Columns("AV")
Set rngFound = rngToSearch.Find(What:="closed", _
LookIn:=xlFormulas, _
LookAt:=xlWhole, _
MatchCase:=False)
If rngFound Is Nothing Then
MsgBox "There are no items to move."
Else
Set rngFoundAll = rngFound
strFirstAddress = rngFound.Address
Do
Set rngFoundAll = Union(rngFound, rngFoundAll)
Set rngFound = rngToSearch.FindNext(rngFound)
Loop Until rngFound.Address = strFirstAddress

rngFoundAll.EntireRow.Copy Destination:=rngPaste
rngFoundAll.EntireRow.Delete
End If
End Sub

I have solved my query thanks to Ron de Bruin's "Find value in Range, Sheet
or Sheets with VBA" tip sheet. What a great resource.

Replaced LookIn:=xlFormulas with LookIn:=xlValues

"Janelle S" wrote:

Thanks Roger

I changed the variable to read "non current" as suggested but I still get
the pop up msg ""There are no items to move." Any other suggestions?

"Roger Govier" wrote:

Hi Janelle

Are you saying the result of the formula in AV gives either "current" or
"non current" as its result?
If so, then just amend 1 line of Jim's code from
Set rngFound = rngToSearch.Find(What:="closed", _
to
Set rngFound = rngToSearch.Find(What:="non current", _

--
Regards
Roger Govier

"Janelle S" wrote in message
...
Thanks to Jim Thomlinson for the following code to move rows to another
worksheet.

This works perfectly where the value in column AV is text however what I
would like is for this column to be a formulae. This formulae is
calculated
on whether the case is closed and on date (giving "current"/"non current"
result). The code doesn't seem to recognise the results if there is
formulae
in column AV. Please help.

Sub MoveStuff()
Dim rngToSearch As Range
Dim rngFound As Range
Dim rngFoundAll As Range
Dim rngPaste As Range
Dim strFirstAddress As String

Set rngPaste = Sheets("_Non Current").Cells(Rows.Count, _
"A").End(xlUp).Offset(1, 0)
Set rngToSearch = ActiveSheet.Columns("AV")
Set rngFound = rngToSearch.Find(What:="closed", _
LookIn:=xlFormulas, _
LookAt:=xlWhole, _
MatchCase:=False)
If rngFound Is Nothing Then
MsgBox "There are no items to move."
Else
Set rngFoundAll = rngFound
strFirstAddress = rngFound.Address
Do
Set rngFoundAll = Union(rngFound, rngFoundAll)
Set rngFound = rngToSearch.FindNext(rngFound)
Loop Until rngFound.Address = strFirstAddress

rngFoundAll.EntireRow.Copy Destination:=rngPaste
rngFoundAll.EntireRow.Delete
End If
End Sub

Hi Janelle

Glad you got it resolved. Thanks for letting us know
thanks to Ron de Bruin's .... tip sheet. What a great resource
Yes, Ron's site is a wealth of information
--
Regards
Roger Govier

"Janelle S" wrote in message
...
I have solved my query thanks to Ron de Bruin's "Find value in Range,
Sheet
or Sheets with VBA" tip sheet. What a great resource.

Replaced LookIn:=xlFormulas with LookIn:=xlValues

"Janelle S" wrote:

Thanks Roger

I changed the variable to read "non current" as suggested but I still get
the pop up msg ""There are no items to move." Any other suggestions?

"Roger Govier" wrote:

Hi Janelle

Are you saying the result of the formula in AV gives either "current"
or
"non current" as its result?
If so, then just amend 1 line of Jim's code from
Set rngFound = rngToSearch.Find(What:="closed", _
to
Set rngFound = rngToSearch.Find(What:="non current", _

--
Regards
Roger Govier

"Janelle S" wrote in message
...
Thanks to Jim Thomlinson for the following code to move rows to
another
worksheet.

This works perfectly where the value in column AV is text however
what I
would like is for this column to be a formulae. This formulae is
calculated
on whether the case is closed and on date (giving "current"/"non
current"
result). The code doesn't seem to recognise the results if there is
formulae
in column AV. Please help.

Sub MoveStuff()
Dim rngToSearch As Range
Dim rngFound As Range
Dim rngFoundAll As Range
Dim rngPaste As Range
Dim strFirstAddress As String

Set rngPaste = Sheets("_Non Current").Cells(Rows.Count, _
"A").End(xlUp).Offset(1, 0)
Set rngToSearch = ActiveSheet.Columns("AV")
Set rngFound = rngToSearch.Find(What:="closed", _
LookIn:=xlFormulas, _
LookAt:=xlWhole, _
MatchCase:=False)
If rngFound Is Nothing Then
MsgBox "There are no items to move."
Else
Set rngFoundAll = rngFound
strFirstAddress = rngFound.Address
Do
Set rngFoundAll = Union(rngFound, rngFoundAll)
Set rngFound = rngToSearch.FindNext(rngFound)
Loop Until rngFound.Address = strFirstAddress

rngFoundAll.EntireRow.Copy Destination:=rngPaste
rngFoundAll.EntireRow.Delete
End If
End Sub