Macro to Do until Value anywhere on Sheet.

[ronanbaker1]

Hi Guys,

Have a Macro that I want to stop the loop when the ANYWHERE on the sheet does not have the Cell.Value "Net Assets"

This is what I have got but it is not feeding correctly.

Could someone please tell me the adjustment I need?

Thanks

Do While ActiveSheet.Value <> "Net Assets" Is Nothing

 

[Andrew Poulsom]

You can use the Find method. The macro recorder should give you some starter code.

 

[ronanbaker1]

You can use the Find method. The macro recorder should give you some starter code.

I have the Find Method below this in my macro but it comes up 'Debug' when there is no more 'Net Assets'

 

[Andrew Poulsom]

To help with that I would need to see your code.

 

[ronanbaker1]

To help with that I would need to see your code.

Do While ActiveSheet.Value <> "Net Assets" Is Nothing
Range("A1").Select
aa = ActiveCell.Address
Cells.Find(What:="Net Assets", After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
ActiveCell.Offset(7, 0).Select
bb = ActiveCell.Address
Range(aa, bb).Select
Selection.EntireRow.Cut
Sheets.Add After:=Sheets(Sheets.Count)
Cells.Select
ActiveSheet.Paste
Sheets("BSCOMP").Select

Selection.EntireRow.Delete
Loop

 

[Andrew Poulsom]

A Sheet doesn't have a Value property so you can't do this:

Do While ActiveSheet.Value <> "Net Assets" Is Nothing

And I don't know why you need to loop. Assign the result of the Find method to a Range object. Then do nothing if that Range object is Nothing.