Formula Issue.

[Steved]

Hello from Steved

The below formula copies and paste
What do I need to please put in the formula, so that if I
am pasting it gives an option that it takes me to the
next cell and gives me the option to either paste or skip
to the next cell
Thankyou.

Sub ChangeAllValues2()
Dim mySht As Worksheet
Dim myBook As Workbook
Dim ReplaceWith As String
Dim ToReplace As String

ToReplace = Application.InputBox("What value to replace?")
ReplaceWith = Application.InputBox("Replace '" & _
ToReplace & "' with what other value?")

For Each myBook In Application.Workbooks
For Each mySht In myBook.Worksheets
mySht.Cells.Replace _
ToReplace, ReplaceWith, _
xlWhole
Next mySht
Next myBook

End Sub

 

[Dave Peterson]

I think you'd have to use .find first. Then prompt for the response.

If ok, do the change (just one!) and then continue.

But I think I'd just group my sheets, then edit|replace.

This is already built into the Edit|replace dialog.

As a macro:

Option Explicit
Sub testme01()

Dim mySheet As Worksheet
Set mySheet = ActiveSheet
Worksheets.Select
Application.Dialogs(xlDialogFormulaReplace).Show
mySheet.Select

End Sub

And you don't have to be careful checking for cancels!

===

And you may want to get a copy of Jan Karel Pieterse's FlexFind:
http://www.bmsltd.ie/MVP/

There's lots of options in that addin that you may like.

 

[Steved]

Thankyou

-----Original Message-----
I think you'd have to use .find first. Then prompt for the response.

If ok, do the change (just one!) and then continue.

But I think I'd just group my sheets, then edit|replace.

This is already built into the Edit|replace dialog.

As a macro:

Option Explicit
Sub testme01()

Dim mySheet As Worksheet
Set mySheet = ActiveSheet
Worksheets.Select
Application.Dialogs(xlDialogFormulaReplace).Show
mySheet.Select

End Sub

And you don't have to be careful checking for cancels!

===

And you may want to get a copy of Jan Karel Pieterse's FlexFind:
http://www.bmsltd.ie/MVP/

There's lots of options in that addin that you may like.



--

Dave Peterson
(e-mail address removed)
.