Using Popups to supply variables
 

Hi All

I sue this macro to find and replace a number in column I


Columns("I:I").Select
Selection.Replace What:="5.95", Replacement:="2.95", LookAt:=xlPart,
_
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False


I'd like to replace the values in the macro , requesting variable input
via popup. Can someone help?

Effectively , there would be popups to request the column , the search
number and the replace number.

If it could also cycle back to the beginning on completion (unless
cancelled) that would be helpful too.

Grateful for any advice.



Best Wishes

Using Popups to supply variables
 

Look in the vba help index for INPUTBOX

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Colin Hayes" wrote in message
...


Hi All

I sue this macro to find and replace a number in column I


Columns("I:I").Select
Selection.Replace What:="5.95", Replacement:="2.95", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False


I'd like to replace the values in the macro , requesting variable input
via popup. Can someone help?

Effectively , there would be popups to request the column , the search
number and the replace number.

If it could also cycle back to the beginning on completion (unless
cancelled) that would be helpful too.

Grateful for any advice.



Best Wishes

Using Popups to supply variables
 

Sub colin()
Dim colstring As String
Dim findit As Double, replacewith As Double
colstring = Application.InputBox(prompt:="which columns?", Type:=2)
findit = Application.InputBox(prompt:="which value to replace?", Type:=1)
replacewith = Application.InputBox(prompt:="replacement?", Type:=1)
Columns(colstring).Select
Selection.Replace What:=findit, Replacement:=replacewith, LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
End Sub

--
Gary''s Student - gsnu201001


"Colin Hayes" wrote:



Hi All

I sue this macro to find and replace a number in column I


Columns("I:I").Select
Selection.Replace What:="5.95", Replacement:="2.95", LookAt:=xlPart,
_
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False


I'd like to replace the values in the macro , requesting variable input
via popup. Can someone help?

Effectively , there would be popups to request the column , the search
number and the replace number.

If it could also cycle back to the beginning on completion (unless
cancelled) that would be helpful too.

Grateful for any advice.



Best Wishes

.

Using Popups to supply variables
 

In article , Gary''s
Student writes
Sub colin()
Dim colstring As String
Dim findit As Double, replacewith As Double
colstring = Application.InputBox(prompt:="which columns?", Type:=2)
findit = Application.InputBox(prompt:="which value to replace?", Type:=1)
replacewith = Application.InputBox(prompt:="replacement?", Type:=1)
Columns(colstring).Select
Selection.Replace What:=findit, Replacement:=replacewith, LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
End Sub


Hi

OK thanks for your help. I tried it out and it gives an error at the
final hurdle , unfortunately.

I hope I copied over the code properly , as it's all wrapped and I had
to unpick.

This is what I used:

Dim colstring As String
Dim findit As Double, replacewith As Double
colstring = Application.InputBox(prompt:="which columns?", Type:=2)
findit = Application.InputBox(prompt:="which value to replace?",
Type:=1)
replacewith = Application.InputBox(prompt:="replacement?", Type:=1)
Columns(colstring).SelectSelection.Replace What:=findit,
replacement:=replacewith, LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False


It gives an error 'Object doesn't support this property or method'. The
debugger highlights the section from Columns(colstring) onwards.

Hope you can help.



Best Wishes

Using Popups to supply variables
 

I just tested his code with answers as follows:
d:e
5
4
worked just fine

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Colin Hayes" wrote in message
...
In article , Gary''s
Student writes
Sub colin()
Dim colstring As String
Dim findit As Double, replacewith As Double
colstring = Application.InputBox(prompt:="which columns?", Type:=2)
findit = Application.InputBox(prompt:="which value to replace?", Type:=1)
replacewith = Application.InputBox(prompt:="replacement?", Type:=1)
Columns(colstring).Select
Selection.Replace What:=findit, Replacement:=replacewith, LookAt:=xlPart,
_
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
End Sub


Hi

OK thanks for your help. I tried it out and it gives an error at the final
hurdle , unfortunately.

I hope I copied over the code properly , as it's all wrapped and I had to
unpick.

This is what I used:

Dim colstring As String
Dim findit As Double, replacewith As Double
colstring = Application.InputBox(prompt:="which columns?", Type:=2)
findit = Application.InputBox(prompt:="which value to replace?", Type:=1)
replacewith = Application.InputBox(prompt:="replacement?", Type:=1)
Columns(colstring).SelectSelection.Replace What:=findit,
replacement:=replacewith, LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False


It gives an error 'Object doesn't support this property or method'. The
debugger highlights the section from Columns(colstring) onwards.

Hope you can help.



Best Wishes

Using Popups to supply variables
 

In article , Don Guillett
writes
I just tested his code with answers as follows:
d:e
5
4
worked just fine


Hi Don

OK I ran it again , using

I:I
5.95
3.95

and got the same 'Object doesn't support this property or method' errors
I'm afraid.

Whatever I try it gives the same errors , which is frustrating. The code
is wrapped and hard to unpick but I'll keep trying. Glad you got it
going at least.



Best Wishes

Using Popups to supply variables
 

Hi

OK this is fixed and working fine now - thanks for your help. Much
appreciated.

Regards




In article , Colin Hayes
writes
In article , Don Guillett
writes
I just tested his code with answers as follows:
d:e
5
4
worked just fine


Hi Don

OK I ran it again , using

I:I
5.95
3.95

and got the same 'Object doesn't support this property or method' errors
I'm afraid.

Whatever I try it gives the same errors , which is frustrating. The code
is wrapped and hard to unpick but I'll keep trying. Glad you got it
going at least.



Best Wishes