Using Popups to supply variables

[Colin Hayes]

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

 

[Don Guillett]

Look in the vba help index for INPUTBOX

 

[Gary''s Student]

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

 

[Colin Hayes]

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

 

[Don Guillett]

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

 

[Colin Hayes]

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

 

[Colin Hayes]

Hi

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

Regards