CODEWITHSUNDEEP
vbaexcel
Kiran
Kiran

Reputation: 8518

String Concatenation Error in VBA

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

Upvotes: 2

Views: 1167

Answers (2)

user2063626
user2063626

Reputation:

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)"

Upvotes: 4

Jon Egerton
Jon Egerton

Reputation: 41539

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.

Upvotes: 3

Related Questions