Loop until cell is empty

[Ricky Pang]

Hello Experts,
How do you improve upon this code to loop until a cell is empty and
include a message box for each change?

'set String (or Long) = "total operating expenses"
'so that Find: "total operating expenses" = the String
Cells.Find(What:="total operating expenses", After:=ActiveCell, LookIn:=
_
xlFormulas, LookAt:=xlPart, SearchOrder:=xlByColumns,
SearchDirection:= _
xlNext, MatchCase:=False, SearchFormat:=False).Activate
ActiveCell.Offset(-2, 0).Range("A1").Select

'Loop starts here. If cell is blank, then stop. If not, enter the set
String (being "Other Operating Expenses"
If cell = "" Then
ActiveCell.FormulaR1C1 = "Other Operating Expenses"

'Msgbox: [the old word content of this cell] will be replaced by [the
String]. vbYes to Replace and move up another cell. vbNo to not
replace and move up another cell.
ActiveCell.Offset(-1, 0).Range("A1").Select
'Stop moving up any further if cell is blank.
End If
'Loop ends here.

End Sub


Thanks in advance,
Ricky

 

[Leonardo]

Have you tried:

While ActiveCell <> Empty

Your_routine ' Enter your code, looking for desired string and deciding

' if it must be replaced or not

Activecell.Offset(1,0).Select

Wend

Best regards.

 

[Ricky Pang]

Thanks for the Do While...Loop help. Now I'm just missing the msgbox
whether or not to allow change. The part that I need help on is
indicated within this code.

'set String (or Long) = "total operating expenses"
'This is so that it's replaceable for another search word
'in the future
'so that Find: "total operating expenses" = the String

Cells.Find(What:="total operating expenses", After:=ActiveCell, LookIn:=
_
xlFormulas, LookAt:=xlPart, SearchOrder:=xlByColumns, _
SearchDirection:=xlNext, MatchCase:=False,
SearchFormat:=False).Activate
ActiveCell.Offset(-2, 0).Range("A1").Select
Do While ActiveCell <> Empty
ActiveCell.FormulaR1C1 = "Other Operating Expenses"

'Msgbox: [the old word content of this cell] will be replaced by [the
'String]. vbYes to Replace and move up another cell. vbNo to not
'replace and move up another cell.
ActiveCell.Offset(-1, 0).Range("A1").Select
Loop

Thanks again Wend,
Ricky

 

[Guest]

I think! this is what you are looking for:

If MsgBox(" your message ", vbYesNo, "Title") = vbYes then
'your actions if user chooses YES here
Else
'your actions if user chooses NO here
End If

If you just need to do something based on = vbYes, then you can eliminate
the Else section. Note that because we're treating MsgBox as a function, the
parameters have to be enclosed in () as shown above.

 

[Ricky Pang]

Hi JLatham and other Experts,
I'm almost there. How do you improve this code so that the msgbox pops
up only when the activecell is not empty (which it does now) and only
when the activecell is not the s2 String? Presently, it asks to replace
the title when the activecell is already = s2.

Sub OtherOperatingExpense()
Dim s1 As String, s2 As String
s1 = "total operating expenses"
s2 = "Other Expense"

Cells.Find(What:=s1, After:=ActiveCell, LookIn:= _
xlFormulas, LookAt:=xlPart, SearchOrder:=xlByColumns, _
SearchDirection:=xlNext, MatchCase:=False,
SearchFormat:=False).Activate
ActiveCell.Offset(-2, 0).Range("A1").Select

Do While LCase(ActiveCell) = LCase(s2)
ActiveCell.Offset(-1, 0).Range("A1").Select
Loop

Do While ActiveCell <> Empty
If MsgBox("[" & ActiveCell & "]" & " will be replaced with " & "[" &
s2 & "]", vbYesNo, _
"Updating Other Titles Changes") = vbYes Then
ActiveCell = s2
End If
ActiveCell.Offset(-1, 0).Range("A1").Select
Loop
End Sub

Thanks in advance,
Ricky

 

[Guest]

Try

Do ActiveCell <> Empty AND ActiveCell <> s2

 

[Ricky Pang]

Hi JLatham,
I've tried your suggestion. If someone made changes within those cells,
this code would jump only to the first cell that <> s2, make the change,
then it stops. I'm trying to get the code to continue jumping up to the
next <> s2 cell and keep making more changes. Any ideas?

Thanks in advance,
Ricky