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

Question

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!

what are you intending to do? it seems like you are mixing up R1C1 notation and A1. See: lytebyte.com/2008/04/29/… — JMax
Is it not possible to use both? I want to take 2 arguments for my function, one from the cell to the left, and one from another sheet in column "ColumnLetter" and row "i". — Derek

chris neilsen chris neilsen · Accepted Answer · 2011-10-17T08:55:23

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

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

2 Answers