CODEWITHSUNDEEP
vba
Connor Lee
Connor Lee

Reputation: 69

Replacing specific letter in vba

dict = {}
dict['one'] = "This is one"
dict[2]     = "This is two"

tinydict = {'name': 'john','code':6734, 'dept': 'sales'}


print dict['one']       # Prints value for 'one' key
print dict[2]           # Prints value for 2 key
print tinydict          # Prints complete dictionary
print tinydict.keys()   # Prints all the keys
print tinydict.values() # Prints all the values

I tried to add the parentheses to the contents of python code and have to replace '#' to ')#' as hash sign implies comments in editor. 'print' is also replaced to 'print ('
and appended ')' for python code

The result should have shown like code below

print (tinydict.values() )# Prints all the values)

I put this code into empty excel sheet and run the vba code below

Sub macro1()
Set Rng = Range([a1], [p40])

For Each c In Rng
   If IsEmpty(c.Value) = False Then
        If c Like "*print*" Then
            c.Replace _
            What:="print", Replacement:="print (", _
            SearchOrder:=xlByRows, lookat:=xlPart

            c.Value = c.Value & ")"   'appending ) at the end'
        End If

        If c Like "*#*" Then
            c.Replace _
            What:="#", Replacement:=")#", _
            SearchOrder:=xlByRows, lookat:=xlPart
        End If
    End If
Next c

End Sub

however, it works only to second line of the code

print ( dict[2]           )# print (s value for 2 key)

rest of the line which contains '#' has not affected.

I've wondered what causes brought up this.

Tnx

Upvotes: 0

Views: 39

Answers (1)

donPablo
donPablo

Reputation: 1959

You might add Option Explicit to the top of your program. And then add DIM-s for Rng and c. But most importantly the problem is 

If c Like "*#*" Then

which looks for a NUMBER somewhere on the line. This needs to be changed to look for a Pound-Sign by enclosing in square brackets

If c Like "*[#]*" Then

Upvotes: 1

Related Questions