Excel VBA insert formula

Question

i am trying to insert a formula in a cell using vba using a dynamic column reference like this:

Fred = ConvertToLetter(ActiveCell.Column)
Bob = "=DATE(LEFT(" + Fred + "2,4),MID(" + Fred + "2,5,2),MID(" + Fred + "2,7,2))"
ActiveCell.FormulaR1C1 = Bob    

Function ConvertToLetter(iCol As Integer) As String
    Dim iAlpha As Integer
    Dim iRemainder As Integer
    iAlpha = Int(iCol / 27)
    iRemainder = iCol - (iAlpha * 26)
    If iAlpha > 0 Then
        ConvertToLetter = Chr(iAlpha + 64)
    End If
    If iRemainder > 0 Then
        ConvertToLetter = ConvertToLetter & Chr(iRemainder + 64)
    End If
End Function

but when it runs the formula ends up with single quotes around the cell and the formula does not work like this:

=DATE(LEFT('AY2',4),MID('AY2',5,2),MID('AY2',7,2))

how do i stop the single quotes being added to allow the formula to work correctly?

Thanks

there is also a more elegant function to find the column letter [here](http://stackoverflow.com/questions/12796973/vba-function-to-convert-column-number-to-letter) — Scott Craner, Sep 10 '15 at 15:12
Don't know if it *really* matters, but you really ought to use the ampersand not plus sign for string concatenation. — Jon Peltier, Sep 11 '15 at 02:14

Excel Hero · Accepted Answer · 2015-09-10T15:15:13.787

1

It's the FormulaR1C1 property causing the problem.

Do this instead:

ActiveCell.Formula = Bob

By the way, instead of the ConvertToLetter() function, you can use this:

Fred = Split(ActiveCell.Address, "$")(1)

edited Sep 10 '15 at 15:15

answered Sep 10 '15 at 15:11

Excel Hero

14,253
4
33
40

Excellent works a treat, trying to be clever and reusing a recorded macro that where the R1C1 came from.... – Dwayne Dibbley Sep 10 '15 at 15:15

score 0 · Answer 2 · answered Sep 10 '15 at 15:15

0

You can use this '' (two single apostrophes) instead.

answered Sep 10 '15 at 15:15

Tom K.

1,020
1
12
28

score 0 · Answer 3 · answered Sep 10 '15 at 15:22

To use R1C1 formula you'd use:

ActiveCell.FormulaR1C1 = "=DATE(LEFT(R2C,4),MID(R2C,5,2),MID(R2C,7,2))"

R2 in the formula means Row 2. C in the formula means the column the formula is in.

Looking at R1C1 notation:
R[-1] means this row minus 1.
C[1] means this column plus 1.
R1 means row 1
C1 means column 1
R means this row.
C means this column.

So, in cell B2 - R1C1 means cell A1.
R1C[-1] means cell A1
RC1 means cell A2.
RC means cell B2.

Hope that's clear. :)

or you can just use ActiveCell.Formula = Bob as Excel Hero said.

Excel VBA insert formula

3 Answers3