Using Popups to supply variables
in [Functions]
|
From: Colin Hayes on 31 Jan 2010 09:53 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
From: Don Guillett on 31 Jan 2010 10:17 Look in the vba help index for INPUTBOX -- Don Guillett Microsoft MVP Excel SalesAid Software dguillett(a)gmail.com "Colin Hayes" <Colin(a)chayes.demon.co.uk> wrote in message news:G62qvUAXlZZLFwsI(a)chayes.demon.co.uk... > > > 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 >
From: Gary''s Student on 31 Jan 2010 10:41 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 > > . >
From: Colin Hayes on 31 Jan 2010 11:29 In article <48F593FA-E2BB-4058-9DB4-44949A028F77(a)microsoft.com>, Gary''s Student <GarysStudent(a)discussions.microsoft.com> 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
From: Don Guillett on 31 Jan 2010 12:14
I just tested his code with answers as follows: d:e 5 4 worked just fine -- Don Guillett Microsoft MVP Excel SalesAid Software dguillett(a)gmail.com "Colin Hayes" <Colin(a)chayes.demon.co.uk> wrote in message news:b5mdvIAX$aZLFwfF(a)chayes.demon.co.uk... > In article <48F593FA-E2BB-4058-9DB4-44949A028F77(a)microsoft.com>, Gary''s > Student <GarysStudent(a)discussions.microsoft.com> 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 |