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 — Scott Craner
– Scott Craner, Commented Sep 10, 2015 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
– Jon Peltier, Commented Sep 11, 2015 at 2:14

Excel Hero · Accepted Answer · 2015-09-10 15:15:13Z

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, 2015 at 15:15

answered Sep 10, 2015 at 15:11

Excel Hero

14.8k4 gold badges35 silver badges43 bronze badges

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

1 Comment

Dwayne Dibbley Over a year ago

Excellent works a treat, trying to be clever and reusing a recorded macro that where the R1C1 came from....

Tom K. · Accepted Answer · 2015-09-10 15:15:08Z

0

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

answered Sep 10, 2015 at 15:15

Tom K.

1,0421 gold badge12 silver badges28 bronze badges

Comments

Darren Bartrup-Cook · Accepted Answer · 2015-09-10 15:22:43Z

0

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.

answered Sep 10, 2015 at 15:22

Darren Bartrup-Cook

20.2k2 gold badges27 silver badges50 bronze badges

Collectives™ on Stack Overflow

Excel VBA insert formula

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related