Find Button

[FlashGordy]

Afternoon again guys.

I was wondering how one would go about adding a button into a a sheet which, when clicked would find and replace a specific character or value, on another sheet, without requiring confirmation.

Thanks
Gordon

 

[JamesW]

Hi there,

Add your button using the Control Toolbox, double click on it and paste this in:

Private Sub CommandButton1_Click()
    lRow = Sheets("[U][B]Sheet2[/B][/U]").Range("[U][B]A1[/B][/U]").End(xlDown).Row
    Sheets("[B][U]Sheet2[/U][/B]").Range("[U][B]A1:A[/B][/U]" & lRow).Replace What:="[U][B]b[/B][/U]", Replacement:="[U][B]s[/B][/U]"
End Sub

Change the bold and underlined bits to whatever you wish.

 

[FlashGordy]

Hi James

I'm using Excel for Mac. So I'm guessing I right click on the button I created and go to "Assign Macro"? It creates a Macro called "Button1_Click" but will not let me paste anything underneath?

Gordon

 

[JamesW]

Ah, erm... Not sure about Mac unfortunatly.

Try adding this to the module, instead of the click event:

Sub stuff()
    lRow = Sheets("Sheet2").Range("A1").End(xlDown).Row
    Sheets("Sheet2").Range("A1:A" & lRow).Replace What:="b", Replacement:="s"
End Sub

and then assigning the button to 'stuff' - Change the name if you like.

 

[FlashGordy]

I've tried it on a windows machine too, and I'm still unable to edit the macro. I'm kinda new to the whole macro thing, so don't know if I'm just doing something stupid.

How does one "add to the module" and "assign a button"?

 

[FlashGordy]

*dupe*

 

[GK039]

Alt + F11 to open the VB Editor. Go Insert --> Module and then paste the code provided from James

George

I've tried it on a windows machine too, and I'm still unable to edit the macro. I'm kinda new to the whole macro thing, so don't know if I'm just doing something stupid.

How does one "add to the module" and "assign a button"?

 

[JamesW]

**** it George, you're too quick for me!

 

[GK039]

Yes, but you were quicker providing the original solution

**** it George, you're too quick for me!