String Concatenation Error in VBA

Question

I am having some trouble concating 2 strings. Here is what I am doing :

    Dim bound1 As String
    Dim bound2 As String

    bound1 = "E6:E" & 6 + tTest_Threshold
    bound2 = "E" & 6 + index1 & ":E" & 6 + index2

    Worksheets(chipSheet1).Cells(currIteration + 7, 5).FormulaR1C1 = "=TTEST(" & bound1 & "," & bound2 & ",2,1)"

However, it is printing : =TTEST('E6':'E12','E8':'E14',2,1)

I donot understand, why "'" is printed in the Excel formula. Any idea what I am doing wrong in the code ?

Thanks

Try brackets around (6 + tTest_Threshold), (6 + index1) and (6 + index2), perhaps? — Penfold
– Penfold, Commented Mar 28, 2013 at 12:36
I am trying now, But I have never used "()" in string concatenation. Does it remove the "'" from the formula, you think ? — Kiran
– Kiran, Commented Mar 28, 2013 at 12:39
No, It didn't. It is still printing the "'" in the formula. I donot know why.. — Kiran
– Kiran, Commented Mar 28, 2013 at 12:39
OK, sorry. I guessed that it might be doing "E6:E" & 6 (giving "E6:E6"), and then trying to add an integer to that, causing confusion. — Penfold
– Penfold, Commented Mar 28, 2013 at 12:46

user2063626 · Accepted Answer · 2013-03-28 12:42:11Z

4

Use

 Worksheets(chipSheet1).Cells(currIteration + 7, 5).Formula = "=TTEST(" & bound1 & "," & bound2 & ",2,1)"

instead of

Worksheets(chipSheet1).Cells(currIteration + 7, 5).FormulaR1C1 = "=TTEST(" & bound1 & "," & bound2 & ",2,1)"

answered Mar 28, 2013 at 12:42

user2063626

Sign up to request clarification or add additional context in comments.

Comments

Jon Egerton · Accepted Answer · 2013-03-28 12:38:51Z

I suspect its because you're assigning to the FormulaR1C1 property, which expects a different notation pattern that the one you're using. Try filling the Formula property instead.

To quote this MSDN forum post:

Formula returns the formula in A1 notation, FormulaR1C1 returns the formula in R1C1 notation.

By default, Excel uses the A1 reference style, which refers to columns with letters (A through IV, for a total of 256 columns) and refers to rows with numbers (1 through 65536). These letters and numbers are called row and column headings. To refer to a cell, enter the column letter followed by the row number. For example, D50 refers to the cell at the intersection of column D and row 50.

In the R1C1 reference style, both the rows and the columns on the worksheet are numbered. In the R1C1 style, Excel indicates the location of a cell with an "R" followed by a row number and a "C" followed by a column number. For example, the absolute cell reference R1C1 is equivalent to the absolute reference $A$1 in A1 reference style.

Collectives™ on Stack Overflow

String Concatenation Error in VBA

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related