Excel VBA Formula Format Issue

Question

I'm having a particulary awful time understanding what goes wrong here.

I would like to enter the following formula via VBA:

Range("C8").Select
ActiveCell.FormulaR1C1 = "=MODE.SNGL('Data'!D:D)"

Now, the problem is that VBA instead outputs it as:

=MODE.SNGL('Data'!D(D)

I tried using chr(58) and also using ' to escape, but no cherry. Could someone tell me what I am doing wrong?

Others have answered but not that you could use just ActiveCell.Formula = "=MODE.SNGL(Data!D:D)", the pair of ' are redundant — brettdj
– brettdj, Commented Jul 1, 2013 at 13:45

Kazimierz Jawor · Accepted Answer · 2013-07-01 14:03:42Z

3

Use this instead:

ActiveCell.Formula = "=MODE.SNGL('Data'!D:D)"

Or if you need to use .FormulaR1C1 property you could do it this way:

ActiveCell.FormulaR1C1 = "=MODE.SNGL('Data'!C4:C4)"

answered Jul 1, 2013 at 13:43

Kazimierz Jawor

19.1k7 gold badges38 silver badges58 bronze badges

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

2 Comments

fyi since the activecell was in column c the r1c1 version ought to refer to 'Data'!C[1]:C[1] or Data!C4:C4 for absolute references

@JosieP, you right, I improved answer accordingly, thank you!

JosieP · Accepted Answer · 2013-07-01 13:43:11Z

2

if you use FOrmulaR1C1 you have to pass an R1C1 style reference not A1 style. replace that code by

Range("C8").Formula = "=MODE.SNGL('Data'!D:D)"

answered Jul 1, 2013 at 13:43

JosieP

3,4101 gold badge15 silver badges16 bronze badges