How to input a single double quote in my vba Excel code

Question

I have several sheets in a worksheet called 1, 2, 3, 4, 5, 6, 7....

and I need to Copy all the files in 1 single Sheet.

I can't find the way to right now only one " to give the name to the sheet.

The problem happens here:

varNBLIGNES = Sheets(""" & varDC & """).Range(COLNUM & ":" & COLNUM).Cells.SpecialCells(xlCellTypeConstants).Count

'CONSTANTS
Const COLNUM As String = "B" 'PART NUMBERS WILL ALWAYS BE IN THE B COLUM FOR EVERY EXCEL FILES
Const COLPRC As String = "C" 'NEW LIST PRICE WILL ALWAYS BE IN THE C COLUM FOR EVERY EXCEL FILES
Const COLDSC As String = "Q" 'NEW DESCRIPTION WILL ALWAYS BE IN THE Q COLUM FOR EVERY EXCEL FILES
Const COLUOM As String = "S" 'NEW UNIT OF MESURE WILL ALWAYS BE IN THE S COLUM FOR EVERY EXCEL FILES
Const COLUIP As String = "T" 'NEW UNIT IN PACKAGE WILL ALWAYS BE IN THE T COLUM FOR EVERY EXCEL FILES
Const vbDoubleQuote As String = """"

'VARIABLES
Dim varNBLIGNES As Integer
Dim varDC As Integer
Dim varDCS As String
Dim i As Integer
Dim quote As String


'INITIALIZATION
varNBLIGNES = 0
varDC = Sheets(2).Range("P5").Value
varDCS = ""
i = 1
quote = Chr(34)


'SUB CODE TO COMPILE FILES INTO ONE
'While varDC >= i

    varNBLIGNES = Sheets(""" & varDC & """).Range(COLNUM & ":" & COLNUM).Cells.SpecialCells(xlCellTypeConstants).Count
    varNBLIGNES = varNBLIGNES + 1

    Sheets(varDC).Range("B3:" & COLNUM & varNBLIGNES).Copy
    Sheets(4).Select
    Range("B4").Select
    ActiveSheet.Paste


    'Sheets("PROD FOUR").Select
    'Range("B4").Select
    'ActiveSheet.Paste


    'varDC = varDC - 1

'Wend

As you can see I tried to declare it in a CONST, I also tried to create the "CHR(34)" and everything seams to fail.

What am I doing wrong? Thanks you in advance

`""" & varDC & """` is a single string. It sould be `"""" & varDC & """"` (just like in your `vbDoubleQuote` constant) — litelite, Aug 16 '17 at 19:58
But it's not working if I do that, it says error number 9. varNBLIGNES = Sheets(vbDoubleQuote & varDC & vbDoubleQuote).Range(COLNUM & ":" & COLNUM).Cells.SpecialCells(xlCellTypeConstants).Count it says The index does not belong to the selection — Konsume, Aug 16 '17 at 20:04
Error 9 is an [out of range error](https://msdn.microsoft.com/en-us/library/aa264519(v=vs.60).aspx). Maybe name you are passing to `Sheets` is wrong. Are you sure the sheet name actually contains double quotes? — litelite, Aug 16 '17 at 20:07
If thats your sheet names, you don't need the double quotes at all! However, if you pass a number to `Sheets` it will try to get the sheet at index 9 and not the sheet named 9. So you have to cast it to a string or declare `varDC` as String — litelite, Aug 16 '17 at 20:11
lets say in this case that varDC holds 9.... than if I write down Sheets(varDC) it will say Sheets(9). But Sheet(9) isn't the name it's the reference that vba gave to the project sheet. So let's say that my sheet named number 9 is actually Sheet 14 (9),,, and Sheet 9 (1).... thats my problem — Konsume, Aug 16 '17 at 20:11
so something like Sheet(CStr(varDC)) ? I'm trying it right now — Konsume, Aug 16 '17 at 20:14

score -1 · Answer 1 · answered Aug 16 '17 at 19:59

-1

To add quotation marks in a string use a different type of quotation mark, e.g.

"'" & varDC & "'"

answered Aug 16 '17 at 19:59

0liveradam8

752
4
18

1

He want's double quote, not singles – litelite Aug 16 '17 at 20:00

How to input a single double quote in my vba Excel code

1 Answers1