CODEWITHSUNDEEP
vbaexcel
Sagi
Sagi

Reputation: 65

How to remove quotes using vba?

I have:

nuid="!,@,a-z"

But I do not want the double quotes. I want nuid=!,@,a-z

Suggest me ways to remove the start and end quotes

Here is my code:

sub highlight(nuid as string)

dim sh3 as worksheet

Set sh3 = Thisworkbook.Worksheets("Sheet1")

sh3.Select

Cells.Find("User ID").Select

ActiveCell.Offset(1, 0).Select

nuid = Replace(nuid, """", "")

Set rn = sh3.UsedRange
  k = rn.Rows.Count + rn.Row - 1
  For x = 1 To k

 If ActiveCell.Value Like nuid Then

 Selection.Interior.Color = vbYellow

 Else

 Selection.Interior.ColorIndex = xlNone

End If

ActiveCell.Offset(1, 0).Select 'moves activecell down one row.

Next

end sub

From my gui, i will enter special characters which will be stored in the variable nuid.I want only the special characters and not the quotes around it

Upvotes: 3

Views: 40435

Answers (4)

FreeSoftwareServers
FreeSoftwareServers

Reputation: 2791

Proper Function:

Sub Test()
 Debug.Print RemoveOuterQuotes(Cells(2, 1).Value)
End Sub

Public Function RemoveOuterQuotes(ByVal Str As String) As String
 If Left(Str, 1) = """" Then
  Str = Right(Str, Len(Str) - 1)
 End If
 If Right(Str, 1) = """" Then
  Str = Left(Str, Len(Str) - 1)
 End If
 'Debug.Print Str
 'Stop
 RemoveOuterQuotes = Str
End Function

Upvotes: 2

Vasily
Vasily

Reputation: 5782

additional variant

Sub highlight(nuid As String)
    Dim sh3 As Worksheet, Cl&, Lrow&, x&, oCell As Range
    Set sh3 = ThisWorkbook.Worksheets("Sheet1")
    Cl = sh3.Cells.Find("User ID").Column
    Frow = sh3.Cells.Find("User ID").Row + 1
    Lrow = Cells.Find("*", , , , xlByRows, xlPrevious).Row
    For Each oCell In sh3.Range(Cells(Frow, Cl), Cells(Lrow, Cl))
        If oCell.Value <> "" Then
            For x = 1 To Len(nuid)
                If oCell.Value Like "*" & Mid(nuid, x, 1) & "*" Then
                    oCell.Interior.Color = vbYellow
                    Exit For
                Else
                    oCell.Interior.Color = xlNone
                End If
            Next x
        End If
    Next oCell
End Sub

output

enter image description here

but if you need to find, for instance the cells which contain any char in low case [a-z] then another aproach should be used

Upvotes: 0

Dawid
Dawid

Reputation: 786

Also you can try:

nuid = Replace(nuid, Chr(34), vbNullString)

But you can have problem if quotes not the first nor the last character, for example: "!,@,"a-z".

In that case you can try:

nuid = Mid(nuid, 2, Len(nuid) - 1) This will cut the first and last character

Edit: It seems to me that the quotes that you see indicates the type of a variable string.

enter image description here

Edit2 - watch window

enter image description here

Results:

enter image description here

Edit3 - with sub 4 Sagi:

Sub Highlight4Sagi(SpecChar As String)

Dim Column As Integer
SpecChar = "!@#"

ThisWorkbook.Worksheets(1).Select
Column = Cells.Find("User ID").Column

LastRow = Cells.Find("*", searchorder:=xlByRows, searchdirection:=xlPrevious).Row

For i = 2 To LastRow 'loop each row in column "User ID"
    For j = 1 To Len(SpecChar) 'loop every specchar: ! and @ and # and find him in each cells
        If InStr(1, Cells(i, Column), Mid(SpecChar, j, 1)) > 0 Then
        Cells(i, Column).Interior.ColorIndex = 6
        Exit For
        Else
        Cells(i, Column).Interior.ColorIndex = 0
        End If
    Next j
Next i

End Sub

Upvotes: 9

S.Krishna
S.Krishna

Reputation: 876

Basically escape a " with ""

Below should help

nuid = replace (nuid, """", "")

Code Output

Upvotes: 0

Related Questions