replace function

[Dalia]

Hi,

Iam trying to do a replace function but it is not working. my vba code is below

Sub m()
Dim originaltext As String
Dim correctedtext As String
  originaltext = Range("A2").Value
  correctedtext = Replace(originaltext, "david", "safar")
Range("A5").Value = correctedtext
 
 
 

End Sub

can you please tell wat went on wrong

 

[NARAYANK991]

Hi ,

Try this :

Sub m()
    Dim originaltext As String
    Dim correctedtext As String
    originaltext = [A2]
    correctedtext = Replace(originaltext, "david", "safar", , , vbTextCompare)
    [A5] = correctedtext
End Sub

The only change is in the following statement :

correctedtext = Replace(originaltext, "david", "safar", , , vbTextCompare)

The default method of comparison is Binary , which means essentially a character code by character code comparison ; thus david will not match David. The change from Binary comparison to Text comparison means the comparison becomes case insensitive. Thus now , david will match David.

Narayan

 

[Debraj]

Just emphasizing... @NARAYANK991 's line...

Option Compare Text
Sub m()
Dim originaltext As String
Dim correctedtext As String
  originaltext = Range("A2").Value
  correctedtext = Replace(originaltext, "david", "safar")
  Range("A5").Value = correctedtext
End Sub

Where Narayan has set Non-Case-Sensitive approach in Replace Function only.. I have set complete Module's default comparison method as Non-Case-Sensitive.. by adding Option Compare Text at the very first line in the module...

 

[Dalia]

Hi,

Thanks guys... you are making my learning really easy....

 

[Dalia]

Hi,

As we have named the variable " correctedtext as string" can we rearrange the below code

Range("A5").Value = correctedtext

with

 correctedtext=Range("A5").Value

I tried with the second code but its not working. I want to know the reason. Please help me out

 

[NARAYANK991]

Hi ,

But what would that achieve ?

In VB , or for that matter in any programming language , the "=" operator is what is called an assignment operator ; when you write :

correctedtext = Range("A5").value

what you are asking the computer to do is take the value that is in the worksheet cell A5 , and assign that value to , or put that value in , the variable correctedtext ; this is a variable within the procedure ; you cannot make use of it within the worksheet except through the procedure.

In the previous statement :

correctedtext = Replace(originaltext, "david", "safar")

you are assigning the value of originaltext ( another variable ) to correctedtext , after replacing the text david by the text safar.

Now , if you follow that statement with the statement :

correctedtext = Range("A5").value

what happens is that the assignment which was done in the previous statement is overwritten by the assignment in this statement. It is like you have two statements , one after the other :

i = 6
i = 3

The two statements don't do anything , since the assignment of the value 3 to the variable i overwrites the earlier assignment of the value 6.

Instead if you write :

Range("A5").value = correctedtext

what you are doing is putting the modified value of the variable correctedtext into the worksheet cell A5.

Narayan

 

[Dalia]

@NARAYANK991 thank you.... slowly am becoming A BIG FAN of this forum... gathering a trust that anything can be learnt in this forum... specially when there are EXCEL GURUS like you

 

[Dalia]

Hi,

I am trying to do a replace function but in a different way. Kindly advise what went wrong

Sub m()
Dim ProductCode As String
ProductCode = "PD-23-23-45"
ProductCode = Replace(ProductCode, "-", "", , , vbTextCompare)


End Sub

 

[Luke M]

Hi,

I am trying to do a replace function but in a different way. Kindly advise what went wrong

Sub m()
Dim ProductCode As String
ProductCode = "PD-23-23-45"
ProductCode = Replace(ProductCode, "-", "", , , vbTextCompare)


End Sub

Nothing went "wrong". We just didn't do anything with the ProductCode variable. If you run this:

Sub m()
Dim ProductCode As String
ProductCode = "PD-23-23-45"
ProductCode = Replace(ProductCode, "-", "", , , vbTextCompare)

MsgBox ProductCode
End Sub

you'll see that the ProductCode did get changed.

 

[Dalia]

Hi,

Thank you,

but I want to change the productcode which is in cell A1. I dont want a Msgbox. Can you please help me on that

 

[Debraj]

Hi Dalia..

try this..

 Sub m()
    Dim ProductCode As String
    ProductCode = Range("A1").Value
    ProductCode = Replace(ProductCode, "-", "", , , vbTextCompare)
    Range("A1").Value = ProductCode
End Sub