am exporting data from access to excel using vba , and i want to excel to receive these lines:-
=COUNTIF('Sheet1'!G2:G68,"1/1")
=COUNTIF('Sheet1'!G2:G68,"1/2")
etc...
my problem is that i can't add these double quotations right,i know that to print this symbol i should put it twice like this "" but still no luck,am getting all kinds of error,can you please modify my code:=
For r = 6 To 16
.Range("B" & r).Value = "=COUNTIF('Sheet1'!C1:C67,1/" & r - 5 & ")"
Next
thanks
If you wish to embed double quotes into the string you are building you can either do it by (as you mentioned) using two pairs of double quotes to 'escape' the double quote you are attempting to add, as follows:
"=COUNTIF('Sheet1'!C1:C67,""1/" & r - 5 & """)"
...which will return:
=COUNTIF('Sheet1'!C1:C67,"1/-5")
An important point to remember is that in order to properly escape the double quote you are inserting, you dont count the double quotes that are defining the string itself as one of the two pairs; That is why in the above example there are 4 pairs at the end:
""")"
The outer two define the string itself; the inner two represent the escaped double quote character.
I always found this form somewhat confusing, and would often opt to instead insert the double quotes by using the chr function, passing in the ascii value for the double quotes character, which is 34;
"=COUNTIF('Sheet1'!C1:C67," & chr(34) & "1/" & r - 5 & chr(34) & ")"
...which will return the same result as the first example:
=COUNTIF('Sheet1'!C1:C67,"1/-5")
Hope this helps,
nim