CODEWITHSUNDEEP
vbaexcel
Derek
Derek

Reputation: 858

When writing string to a cell, VBA inserts extra single quotation marks

I am trying to write this formula using VBA:

ActiveCell.Value = "=f(R[-1]C[0],Sheet1!" & ColumnLetter & i & ")"

Where ColumnLetter is some variable letter which my macro computes earlier, and f is some function, and i is some number.

The problem is that when I run this, the cell is given this instead: (if ColumnLetter = F, i = 16):

=f(R[-1]C[0],Sheet1!'F16')

but I want:

=f(R[-1]C[0],Sheet1!F16)

Why is VBA or Excel putting those single quotation marks around F16? It does not insert these extra quotation marks if I do not include R[-1][0] as an argument in my formula, but I need to include this.

Help much appreciated!

Upvotes: 7

Views: 10406

Answers (2)

chris neilsen
chris neilsen

Reputation: 53136

Its the combination of R1C1 and A1 addressing. You need to pick one method and use it for both parts.
Note that if you type =f(R[-1]C[0],Sheet1!F16) into a cell you will get an error for the same reason.

You say you need to use R1C1 style for the first address, but (assuming this is because you don't want absolute address) you can use .Offset instead

ActiveCell.Value = "=f(" & ActiveCell.Offset(-1, 0).Address(False, False) _
 & ",Sheet1!" & ColumnLetter & i & ")"

Upvotes: 10

Delcon
Delcon

Reputation: 2545

The Apostrophe means to Excel that it should interpret it as text.

Write it to ActiveCell.Formula. That way it is recognized as Formula.

Upvotes: 2

Related Questions