Hi Danny,
Try this one
Function ReplaceChars(Word As String, Oldstring As String, NewString As String) As String
Dim i As Integer
Dim OLength As Integer
Dim NLength As Integer
OLength = Len(Oldstring)
NLength = Len(NewString)

For i = 1 To Len(Word)
    If UCase(Mid(Word, i, OLength)) = UCase(Oldstring) Then
        ReplaceChars = Mid(Word, 1, i - 1) & NewString & Mid(Word, i + OLength)
        Exit For
    End If
Next
End Function

You can call a function like this example shows

Sub CallFunction()
    ActiveCell = ReplaceChars("Old Word", "old", "new")
End Sub


-vemaju

Hi Vemaju,
I tried using your function somehow it does not work, it just replaced any first cell like : first cell select from "Bruce " to .... the first cell "Bruce" replaced to new. As my question it just replace any cell(on that column) has a character.
Please take a look.
Thanks
D

D

Hi,

Try to do it this way

For i = 1 To Len(Word)
    If UCase(Mid(Word, i, OLength)) = UCase(Oldstring) Then
        ReplaceChars = Mid(Word, 1, i - 1) & NewString & Mid(Word, i + OLength)
        Exit For
    End If
Next
If Len(ReplaceChars) = 0 Then
    ReplaceChars = Word
End If
End Function

Sub CallFunction()
Dim Cell As Range
For Each Cell In Selection
    Cell.Value = ReplaceChars(Cell.Value, "old", "new")
Next
End Sub
-vemaju

Hi Vemaju,
The Macro you'd writen did not work, I tried couple times, it did not place the Range has one Character to New Character: "Tr".
Please take a look.
Thanks
D

D

Hi,

I don´t understand what you mean?
Some examples.

-vemaju

Hi,
I mean in the column some range has one character, I would to remove any range in that column has one character, replace to "Tr". The function you have written, it did not work.
I hope you understand me.
Thanks
D

D

Hi,

This code works if one cell has one or more characters

Function ReplaceChars(Word As String, Oldstring As String, NewString As String) As String
Dim i As Integer
Dim OLength As Integer
Dim NLength As Integer
OLength = Len(Oldstring)
NLength = Len(NewString)

For i = 1 To Len(Word)
    If UCase(Mid(Word, i, OLength)) = UCase(Oldstring) Then
        ReplaceChars = Mid(Word, 1, i - 1) & NewString & Mid(Word, i + OLength)
        Exit For
    End If
Next
If Len(ReplaceChars) = 0 Then
    ReplaceChars = Word
End If

End Function

'You can call a function like this example shows

Sub CallFunction()
Dim Cell As Range
For Each cell In Selection
    Cell.Value = ReplaceChars(Cell.Value, "e", "tr")
Next
End Sub

-vemaju

Hi,
Your function you wrote it works good, do you have any smart function to automatic to remove any range has one character: "A, b, c d.....", instead to "Tr". Now you function does work for replace the character you one to remove as "e" to "Tr".
Thanks ahead
D

D