Hi I am trying to add a "replace" function to a macro so that a part of an
equation in a worksheet can be changed. The syntax being used is
Cells.Replace What:="200907", Replacement:="200906", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False
but I want the user to be able to change the 'What' and 'Replacement' values
via a dialogue box. Can anyone help please?

I'm used to Excel 2003, but I imagine the InputBox function would work. You
could ask the user to enter the old and new values separated by a comma, or
you could do the InputBox twice, once for the old value and once for the new.
The InputBox function is built into VBA; you'll find it documented not in
the VBA/Excel help but in the Help for VBA itself.

I dunno as it's the smoothest way to do it, but it would at least work. If
you want to bring up a form with two textboxes in it, you'd have to resort to
VB Forms, which I think will work with Excel but I've never tried them yet so
I can't be much help with them.

Another possibility is just having the user enter the values in two cells
somewhere, before the macro starts, and your macro can just pull the values
from there. It might even be possible - I've never tried it - to stop the
macro, instructing the user to enter the values in two cells and THEN
resuming the program. But that might cause more trouble than it would save,
even if it could be done.

--- "RobJ" wrote:
Hi I am trying to add a "replace" function to a macro so that a part of an
equation in a worksheet can be changed. The syntax being used is
Cells.Replace What:="200907", Replacement:="200906", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False
but I want the user to be able to change the 'What' and 'Replacement' values
via a dialogue box. Can anyone help please?

Thanks Bob that worked a treat. I used 2 input boxes giving the names
'varname' and 'varname1' and the syntax below

varname = InputBox("Please blah blah below", _
"Change From", "200906.xls")
varname1 = InputBox("Please blah blah containing the NEW data in the
format shown below", _
"Change To", "200907.xls")

Cells.Replace What:=varname, Replacement:=varname1, LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False
Don't know why I did not think of that my self.

Thanks again

"Bob Bridges" wrote:

I'm used to Excel 2003, but I imagine the InputBox function would work. You
could ask the user to enter the old and new values separated by a comma, or
you could do the InputBox twice, once for the old value and once for the new.
The InputBox function is built into VBA; you'll find it documented not in
the VBA/Excel help but in the Help for VBA itself.

I dunno as it's the smoothest way to do it, but it would at least work. If
you want to bring up a form with two textboxes in it, you'd have to resort to
VB Forms, which I think will work with Excel but I've never tried them yet so
I can't be much help with them.

Another possibility is just having the user enter the values in two cells
somewhere, before the macro starts, and your macro can just pull the values
from there. It might even be possible - I've never tried it - to stop the
macro, instructing the user to enter the values in two cells and THEN
resuming the program. But that might cause more trouble than it would save,
even if it could be done.

--- "RobJ" wrote:
Hi I am trying to add a "replace" function to a macro so that a part of an
equation in a worksheet can be changed. The syntax being used is
Cells.Replace What:="200907", Replacement:="200906", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False
but I want the user to be able to change the 'What' and 'Replacement' values
via a dialogue box. Can anyone help please?