Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code to find and replace words inside of a text box
I have Office XP with Windows XP Pro.
Is there a tool available or a work around which will allow me to edit - find/replace words inside of a text box. I have a text box that requires edit - find/replace each time the spreadsheet is run. I need this to be done via VBA and would appreciate anyone providing information how to do, or if it can be done. ------------------------------------ Related Subject: Edit - Find/replace, manual or VBA, does not work if the cell being edited contains more than 255 characters. Is there a workaround this? (via VBA) I numerous cells that will contain more than 255 characters that require Edit- Find/Replace. Thanks in advance BOB REYNOLDS |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code to find and replace words inside of a text box
Turn on the macro recorder and to it manually. Turn off the macro recorder.
Study the code. You can certainly replace the hard coded argument values with variables containing values you have elicited from the user. If it doesn't work with more than 255 characters, then that is a limitation (and I agree it doesn't). You would have to go cell by cell and use the vba replace function. this demonstrates that the vba replace works with long strings: Sub BB() Dim i As Long, icnt As Long Dim s1 As String, s As String For i = 1 To 32000 s1 = Chr(Int(Rnd() * 26 + 65)) If s1 = "A" Then icnt = icnt + 1 s = s & s1 Next Debug.Print Len(s) - Len(Replace(s, "A", "")), icnt End Sub -- Regards, Tom Ogilvy "RJR" wrote: I have Office XP with Windows XP Pro. Is there a tool available or a work around which will allow me to edit - find/replace words inside of a text box. I have a text box that requires edit - find/replace each time the spreadsheet is run. I need this to be done via VBA and would appreciate anyone providing information how to do, or if it can be done. ------------------------------------ Related Subject: Edit - Find/replace, manual or VBA, does not work if the cell being edited contains more than 255 characters. Is there a workaround this? (via VBA) I numerous cells that will contain more than 255 characters that require Edit- Find/Replace. Thanks in advance BOB REYNOLDS |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
find and replace inside comments? | Excel Discussion (Misc queries) | |||
counting text example of a cell with multiple words inside | Excel Discussion (Misc queries) | |||
counting text example of a cell with multiple words inside | Excel Discussion (Misc queries) | |||
How can i find and replace words mean from one file to another?? | Excel Programming | |||
Find and Replace formatting inside of a macro | Excel Programming |