Ref. Excel 97

I use a macro to programmatically substitute a certain value from a
formula,

If i want to substitute "A18" whith "B17" in the following formula

=IF(LEN(A18)=0,"",IF(ISERR(FIND(" ",A18)),A18,LEFT(A18,FIND("
",A18)-1)))



I select the cell containing the formula and i Run my macro, that has
two inputbox, one for the old value to be replaced in the formula, one
for the new value.

To select the Old and New values in the inputbox, i have two ways.
1) entereing values in the inputbox manually.
2) selecting a cell, for example range "B17",
( in the inputbox i see B17, but if i change selection in F15 and i go
back in B17, the inputbox show $B$17, and i don't like that).




I go around useing something like
..Address(False, False) instead of .Address.

The following Macro works but, i don't like to see $B$17.
if i want absolute reference i enter $ manually.


Any Help Apreciated.

Best Regards.
Robert.







'This is My Macro

Sub a__Replace_Input_range_2()
On Error Resume Next

Dim My_Default As String

If ActiveCell.Column 1 Then
My_Default = ActiveCell.Offset(0, -1).Address(False, False)
Else

If ActiveCell.Row 1 Then
My_Default = ActiveCell.Offset(-1, 0).Address(False, False)
End If
End If

Dim Old_Value As Range
Dim New_Value As Range

Set Old_Value = Application.InputBox(prompt:="Select Old",
Title:="Title", Default:=My_Default, Type:=8)
If Old_Value Is Nothing Then
MsgBox "Good By"
Exit Sub
End If

Set New_Value = Application.InputBox(prompt:="Select New",
Title:="Title", Default:=My_Default, Type:=8)
If New_Value Is Nothing Then
MsgBox "Good By"
Exit Sub
End If

Selection.Replace What:=Old_Value.Address(False, False),
Replacement:=New_Value.Address(False, False)
End Sub

With no error checking, this works:
Sub Subst()
On Error Resume Next
old = InputBox("Replace what?")
nw = InputBox("with what?")
Selection.Formula = Application.Substitute(Selection.Formula, old, nw)
End Sub

Bob Umlas
Excel MVP

"Bubu" wrote in message
om...
Ref. Excel 97

I use a macro to programmatically substitute a certain value from a
formula,

If i want to substitute "A18" whith "B17" in the following formula

=IF(LEN(A18)=0,"",IF(ISERR(FIND(" ",A18)),A18,LEFT(A18,FIND("
",A18)-1)))



I select the cell containing the formula and i Run my macro, that has
two inputbox, one for the old value to be replaced in the formula, one
for the new value.

To select the Old and New values in the inputbox, i have two ways.
1) entereing values in the inputbox manually.
2) selecting a cell, for example range "B17",
( in the inputbox i see B17, but if i change selection in F15 and i go
back in B17, the inputbox show $B$17, and i don't like that).




I go around useing something like
.Address(False, False) instead of .Address.

The following Macro works but, i don't like to see $B$17.
if i want absolute reference i enter $ manually.


Any Help Apreciated.

Best Regards.
Robert.







'This is My Macro

Sub a__Replace_Input_range_2()
On Error Resume Next

Dim My_Default As String

If ActiveCell.Column 1 Then
My_Default = ActiveCell.Offset(0, -1).Address(False, False)
Else

If ActiveCell.Row 1 Then
My_Default = ActiveCell.Offset(-1, 0).Address(False, False)
End If
End If

Dim Old_Value As Range
Dim New_Value As Range

Set Old_Value = Application.InputBox(prompt:="Select Old",
Title:="Title", Default:=My_Default, Type:=8)
If Old_Value Is Nothing Then
MsgBox "Good By"
Exit Sub
End If

Set New_Value = Application.InputBox(prompt:="Select New",
Title:="Title", Default:=My_Default, Type:=8)
If New_Value Is Nothing Then
MsgBox "Good By"
Exit Sub
End If

Selection.Replace What:=Old_Value.Address(False, False),
Replacement:=New_Value.Address(False, False)
End Sub

"Bob Umlas" wrote
With no error checking, this works:
Sub Subst()
On Error Resume Next
old = InputBox("Replace what?")
nw = InputBox("with what?")
Selection.Formula = Application.Substitute(Selection.Formula, old, nw)
End Sub

Bob Umlas
Excel MVP


If You read n°2 You understand the user may select a range

To select the Old and New values in the inputbox, i have two ways.
1) entereing values in the inputbox manually.

2) selecting a cell, for example range "B17",
( in the inputbox i see B17, but if i change selection in F15 and i go
back in B17, the inputbox show $B$17, and i don't like that).



By the way, thanks.
Best Regards.
Robert.

For Case2:

Try using the .Address property of the selected cell with the following
parameters.
.Address(False, False, xlA1)

For example:
MsgBox ActiveCell.Address(False, False, xlA1)

Troy


"Bubu" wrote in message
om...
Ref. Excel 97

I use a macro to programmatically substitute a certain value from a
formula,

If i want to substitute "A18" whith "B17" in the following formula

=IF(LEN(A18)=0,"",IF(ISERR(FIND(" ",A18)),A18,LEFT(A18,FIND("
",A18)-1)))



I select the cell containing the formula and i Run my macro, that has
two inputbox, one for the old value to be replaced in the formula, one
for the new value.

To select the Old and New values in the inputbox, i have two ways.
1) entereing values in the inputbox manually.
2) selecting a cell, for example range "B17",
( in the inputbox i see B17, but if i change selection in F15 and i go
back in B17, the inputbox show $B$17, and i don't like that).




I go around useing something like
.Address(False, False) instead of .Address.

The following Macro works but, i don't like to see $B$17.
if i want absolute reference i enter $ manually.


Any Help Apreciated.

Best Regards.
Robert.







'This is My Macro

Sub a__Replace_Input_range_2()
On Error Resume Next

Dim My_Default As String

If ActiveCell.Column 1 Then
My_Default = ActiveCell.Offset(0, -1).Address(False, False)
Else

If ActiveCell.Row 1 Then
My_Default = ActiveCell.Offset(-1, 0).Address(False, False)
End If
End If

Dim Old_Value As Range
Dim New_Value As Range

Set Old_Value = Application.InputBox(prompt:="Select Old",
Title:="Title", Default:=My_Default, Type:=8)
If Old_Value Is Nothing Then
MsgBox "Good By"
Exit Sub
End If

Set New_Value = Application.InputBox(prompt:="Select New",
Title:="Title", Default:=My_Default, Type:=8)
If New_Value Is Nothing Then
MsgBox "Good By"
Exit Sub
End If

Selection.Replace What:=Old_Value.Address(False, False),
Replacement:=New_Value.Address(False, False)
End Sub

( in the inputbox i see B17, but if i change selection in F15 and i go
back in B17, the inputbox show $B$17, and i don't like that).

If You Please Read, it mean that i don't see like $B$17, but i do see
like B17,
in my inputbox.
Only when i change selection , because it has to permit user to change
selection, ( Your solution does not allowed it ), it give me problem
showing absolute reference.
As i post already, i go aroud again ...

Selection.Replace What:=Old_Value.Address(False, False),
Replacement:=New_Value.Address(False, False)

But it would be nicer when a user select a range to see directly
in the inputbox like B17 and not like $B$17.


Any Help Apreciated.

Best Regards.
Robert.

If I understand correctly, you are using the following VBA code:
vReturn = Application.InputBox("This is the prompt", "This is the title", ,
, , , , 8)

If that is the case, then the answer to your question is that you can't
control the display in the Application.InputBox while the user is selecting
a cell. As far as I know, the InputBox method does not expose any properties
to control what is displayed. The display will show $B$17.

The previous suggestions were in reference to what you can do after the user
clicks OK.

Troy


"Bubu" wrote in message
om...
( in the inputbox i see B17, but if i change selection in F15 and i go
back in B17, the inputbox show $B$17, and i don't like that).

If You Please Read, it mean that i don't see like $B$17, but i do see
like B17,
in my inputbox.
Only when i change selection , because it has to permit user to change
selection, ( Your solution does not allowed it ), it give me problem
showing absolute reference.
As i post already, i go aroud again ...

Selection.Replace What:=Old_Value.Address(False, False),
Replacement:=New_Value.Address(False, False)

But it would be nicer when a user select a range to see directly
in the inputbox like B17 and not like $B$17.


Any Help Apreciated.

Best Regards.
Robert.

Thank You anyway.

Best Regards.
Robert.