REPLACE method using vba in excel

[avivit]

Hi,

I'd like to use the replace method in vba in excel, in order to replace a certain substring in all column A strings, with another substring, and the result to be in B column.

1. How do I implement this for the whole column (or selected range in col A)?

2. How do I activate this vba procedure? A button in the header?
I know how it works in MS-ACCESS but not in Ecxel.

Thanks

 

[dyarwood]

This is the code for the replace

Columns("A:A&quot.Select
Selection.Replace What:="Name", Replacement:="Name", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False


But I think you can also get the dialog box up by using

Application.Dialogs(the name of the dialog box).Show

dyarwood

 

[GlennUK]

Does this have to be in VBA?

You can use this formula in column B:

=SUBSTITUTE(A1,"original string","replacement string&quot

and copy it down as necessary.


Glenn.

 

[mdav2]

Can you give an example of the data.

I am assuming that the data is caharacter based and you want to store the first 3 values of A in B.
For example: Column A has the value ABCDEFGH and you want to show just the first 3 values in Column B, ABC. Here is the code for I how would do it.

To run it from a button you would need to use the forms toolbar and draw a button. Then choose a macro to assign.

CODE
----
Sub test()

Dim CellValue As String
Dim NewCellValue As String

' Move to start position
[a1].Select
' Put first value into variable for checking
CellValue = ActiveCell.Value

' Check for value before beginning loop
If Trim(Len(CellValue)) > 0 Then
' Loop until cell in column 1 are empty
Do While Trim(Len(CellValue)) > 0

' Move cell to column 2
ActiveCell.Offset(0, 1).Range("A1&quot.Select

' Put first three characters of column A value into column B
NewCellValue = Mid(CellValue, 1, 3)
ActiveCell.Value = NewCellValue

' Move to the next value in column A
ActiveCell.Offset(1, -1).Range("A1&quot.Select
CellValue = ActiveCell.Value

Loop
Else
MsgBox "No data found in first cell, exiting macro", vbOKOnly, "error"
End If

End Sub

 

[avivit]

GlennUK, well I do not want to drag (Btw- is it possible to drag to whole column, or toa certain cell without actually drag? I think there is sone shortcut). I want, each time, to use a different data, and copy to the excel file, and exceute the replace part automatically as much as possible.
Besides, I have more than one replaces to make in each cell:
Replace $ and other characters with nothing, and also replace any space with none, so all will be one string with no spaces.

dyarwood , I see that the result will be in the same column. I'd like to see the result in B column (though I can copy A column to B and then do it).
Also, how do I "name the dialog box, and how do I fill the data in,before saving it"?
And how do I activate the code? When? Is there another way than using a button?

mdav, looks good. What other way is there than using a button to activate this?

Thanks all

 

[xlbo]

avivit - for future reference, there is a VBA specific forum. If you have specific VBA queries, please post there instead. If it is a bit of a mixed query then this forum is fine.....

Rgds, Geoff
[blue]Si hoc signum legere potes, operis boni in rebus Latinus alacribus et fructuosis potiri potes![/blue]
Want the [red]best[/red] answers to your questions ? faq222-2244