SUBSTITUTE FUNCTION

[talytech]

I'm trying to use the SUBSTITUTE excel worksheet function in VBA. I have a cell (C5) that displays text. Whenever the user types in a string that contains double quotations, I want excel to substitute the double quotes for single quotes. I put the following formula in cell C5 and it returned a circular reference error. =SUBTITUTE(C5,"""","'")

Does anyone know how to achieve this task?

[Bob Phillips]

You cannot put the cell in C5 and refer to C5, those two cells must be independent cells.

[talytech]

I know this but do you know how to achieve what I'm asking. My excel form looks like this:

FirstName:C5
Comment:C10

In the comment field, if I type "The Books", I want it to convert to 'The Books'.

[Bob Phillips]

Then it should work, there is no circular reference there unless C5 refers to C10.

And it is SUBSTITUTE not SUBTITUTE but I guess you know that too.

[talytech]

Never mind. I see you're too busy being sarcastic.

[lucas]

It would help if you would post your workbook with an example of what you want to accomplish in the cells so folks don't have to guess. I am personally confused as to what your trying to do.

[Paul_Hossler]

C5

"The Books" (really has quotes)


C10 would be

=SUBSTITUTE(C5,CHAR(34),CHAR(39))

since 34 is the ASCII value of the double quote, and 39 is the single quote

The VBA would be Chr(34) etc

[talytech]

Thanks to all who offered assistance with my issue. I have solved my own problem. I will try to explain what I was doing. I am appending data to an Access database. One of the fields in the access table is [Comments]. The cell that appends to the [comments] field may contain the double quotes and single quotes therefore my sql statement will produce an error.

For example, the value in cell C5 is: the name of the book is "Jumpin Jack Flash".

myComments = C5

In order for the code below to run properly, I have to find all ocurrences of the double qoutes and replace them with single quotes. So what I did was set myComments to:

[VBA]Replace(Worksheets("Sheet1").Range("c5").Value, """", "'")[/VBA]
now c5 will generate the name of the book is 'Jumpin Jack Flash'.

[VBA]sql = "INSERT INTO tblExceptions ( FName, LName, Mid, Comments) SELECT '" & emplFname & "' AS Expr1, '" & emplLname & "' AS Expr2, '" & emplMid & "' AS Expr3, '" & mycomments & "'AS Expr4)[/VBA]

Hopefully, that was a little more clear. I have a hard time explaining what I'm trying to do sometimes. Again, thanks for all the sincere replies.