Working with Strings

[Philosophaie]

I want to know how to make a double quote, ", appear in a string of characters so I can use them in my VBA code.

Dim cell1 as Range
Dim cell2 as Range
Dim VBArange as String
cell1="A4"
VBAstring = "Range(" & """ & cell1 & """ & ")=1"

obviously the """ does not work. What I am looking for is a replacement to display the " in the Range.

 

[shg]

What is the resulting string supposed to be?

 

[Philosophaie]

Range("A4")=1

 

[shg]

    Dim s As String
    Dim cell As Range
    
    Set cell = Range("A4")
    s = "Range(""" & cell.Address(False, False) & """)"
    MsgBox s

 

[Marcelo Branco]

Or

Sub test1()
    Dim VBArange As String, cell1 As String
    cell1 = "A4"
    VBArange = "Range(" & """" & cell1 & """" & ")=1"
    MsgBox VBArange
End Sub

M.

 

[xenou]

I don't mind the doubled, tripled, quadrupled quotes and so on but some prefer using the Chr() function, so another variation on the theme is:

VBAstring = "Range(" & Chr(34) & cell1 & Chr(34) & ")=1"

this is always a good one in a pinch and probably makes code easier to read in some cases.

 

[Philosophaie]

I had to use CStr(cell1) to get it to work but I keep getting "Error 2015" when a function is in the cell. Any thoughts?

 

[xenou]

What's the complete function you are using?
ξ

 

[Philosophaie]

=Function1(B2, C2, D2)

The function works. It gives the correct number result from row 2 columns B,C,D.

It just gives the "Error 2015" when converted to a string!

 

[xenou]

=Function1(B2, C2, D2)

This is what's in the cell, but what is Function1 ... ?