clarity on combination of ampersand and quotes

[monoj chakraborty]

i have a code something like this and its working thanks to help from this forum itself. The code goes something like this :
[
]
my doubt is that I have used the ampersand and double quote combination as trial and error without understanding the logic.

The excel formula looks like this :


"cleaner" is the category variable here and "01jul_fy17" is the p1 variable in the vba code.

While the excel formula looks the same for category and P1 variables, the vba code reads slightly different in terms of placement of the quotes, ampersands and the number of double quotes.

can someone help me with understanding this syntax, so that i can use the same knowledge with proper understanding in my next programmes

thanks

 

[NARAYANK991]

Hi ,

Never post an image , if you can avoid it ; it is not help-friendly.

Post the formula itself ; it is not a problem for you , and at the same time , it will make it easier for forum members to help.

Narayan

 

[NARAYANK991]

Hi ,

When trying to understand the correlation between a worksheet formula and a VBA version of the same formula , try to switch to R1C1 style of the worksheet formula , so that a direct comparison becomes easier.

The worksheet formula you have posted is in A1 style notation. Change the worksheet options to R1C1 style and note down the formula.

Second , never assign a formula directly within the VBA code ; always assign the formula to a VBA variable , and then assign this to the worksheet cell ; for example , you would have the following :

Dim formulastring as String
formulastring = "=sum(offset(" & "'" & country & "'!" & "r" & spanstart & "c" & colrange & ":r" & spanend & "c" _
& colrange + 11 & ",0,(match(" & """" & P1 & """" & "," & "'" & country & "'!" & "r" & spanstart - 1 & "c" & _
colrange & ":r" & spanstart - 1 & "c" & colrange + 11 & ",0)-1)" & "," & (spanend - spanstart + 1) & _
",(match(" & """" & P2 & """" & "," & "'" & country & "'!" & "r" & spanstart - 1 & "c" & colrange & ":r" _
& spanstart - 1 & "c" & colrange + 11 & ",0)-match(" & """" & P1 & """" & "," & "'" & country & "'!" & "r" & _
spanstart - 1 & "c" & colrange & ":r" & spanstart - 1 & "c" & colrange + 11 & ",0)+1))*(" & "'" & country & "'!" _
& "r" & spanstart & "c3:r" & spanend & "c3=""" & Category(rowval - 3) & """))/1000"

With Sheets("category summary by quarter").Cells(catrow, shiftcol)
.FormulaArray = formulastring
End With

The advantage of this is that you can put a breakpoint on the highlighted line of code , so that when you run the macro , execution will halt when it comes to that line.

Now , in the Immediate window , you can enter :

?formulastring

and see whether what is displayed matches what you noted down earlier as the R1C1 version of the worksheet formula.

If it does not , you can make modifications to the formula , re-assign it to the VBA variable formulastring , and again display it ; this process can continue is Debug mode till the contents of the VBA variable formulastring are identical to the R1C1 style version of the worksheet formula.

Once this is achieved , you can rest assured that the formula will work the way you want it to.

As far as the & symbol goes , it is to concatenate two strings , in which case it should appear outside of any quotes ; if the & symbol is a part of the formula , then it has to appear in between a quote pair.

As far as the " symbol goes , a single " character in a worksheet formula becomes two " characters in VBA code ; thus a portion such as :

B1 <> ""

would be represented as :

B1 <> """"

Narayan

 

[monoj chakraborty]

Narayan,

thanks for this.

My doubt is as follows (ref your string as above) :

1. In the part of the formula where the variable called P1 is used, we are using 2 pair of double quotes and the ampersands precede and follow the pairs of double quotes, but
2. While using the variable called category (and both P1 and category are string variables), we are using a single pair and the ampersands are within the pair of the quotes

this is where i am getting confused

thanks for helping me navigate this....

 

[NARAYANK991]

Hi ,

Let us take the two segments which seem to be confusing :

1. """" & P1 & """"

2. "c3=""" & Category(rowval - 3) & """))/1000"

To see what the first one resolves to , enter the following in the Immediate window after you have inserted a breakpoint at the appropriate line of code :

?"""" & P1 & """"

See what is displayed.

Do the same for the second segment , by entering the following :

?"c3=""" & Category(rowval - 3) & """))/1000"

See what is displayed.

---------------------------------------------------------------------------------------

You can rewrite both of the above segments as follows , with identical results :

To see this , let us expand the first segment , to include a little more of the formula ; enter the following portion in the Immediate window :

?",0,(match(" & """" & P1 & """" & ","

See what is displayed.

Rewrite the above as :

?",0,(match(""" & P1 & ""","

See what is displayed.

---------------------------------------------------------------------------------------

For the second segment , rewrite it as follows :

?"c3=" & """" & Category(rowval - 3) & """" & "))/1000"

See what is displayed.

---------------------------------------------------------------------------------------

To clearly understand what is going on , let us put the two versions one below the other , as follows :

1a. ",0,(match(" & """" & P1 & """" & ","

1b. ",0,(match(""" & P1 & ""","

In both the versions , instead of a single double quote in the worksheet formula , we have two double quotes in the VBA code.

The difference is that in the first version , each pair of double quotes , which actually represent a single double quote , are on their own as a text string , which is why they are enclosed within quotes !

In the second version , each pair of double quotes , which actually represent a single double quote , are a part of a string , and the entire string is enclosed within quotes.

--------------------------------------------------------------------------------------

If we repeat the above exercise for the second segment , we have :

2a. "c3=""" & Category(rowval - 3) & """))/1000"

2b. "c3=" & """" & Category(rowval - 3) & """" & "))/1000"

Here , it is the other way round.

In the first version , each pair of double quotes , which actually represent a single double quote , are a part of a string , and the entire string is enclosed within quotes.

In the second version , each pair of double quotes , which actually represent a single double quote , are on their own as a text string , which is why they are enclosed within quotes !

Narayan

 

[monoj chakraborty]

narayn - you have made it absolutely clear. thanks a bunch for the help and the promptness and seriousness