Remove Quotes from Inside of Formula VBA

Question

I am looking to autofill an equation to an entire row.

The problem I am facing is when the equation autofills it puts quotes around the cell reference. When it returns the equation the cell reads =DATEVALUE("E2").

Any tips on how to remove the "" from E2?

Here is my equation below.

Thanks,

Rebecca

Sub formula()

Dim lastRowF As Long


    lastRowF = Range("E" & Rows.Count).End(xlUp).Row
    Range("F2").Select
    ActiveCell.FormulaR1C1 = "=DATEVALUE(E2)"
    Range("F2").Select
    Selection.NumberFormat = "m/d/yyyy"
    Selection.AutoFill Destination:=Range("F2:F" & lastRowF)
    'Range("F2:F248225").Select

End Sub

Answer 1

1. You are doing FormulaR1C1 and E2 is in A1 format. R1C1 is relative to the cell in which the formula is being placed, so in this instance we want to move one column to the left. RC[-1]. Or as stated in the comments, change to .Formula which is A1 based.

2. Do not use Select. See here for more info on avoiding that trap: How to avoid using Select in Excel VBA macros

3. No need for fill down, just assign the formula to all the cells at the same time. Excel will adjust the row number automatically as they are relative and not absolute.

So do this:

Sub formula()

Dim lastRowF As Long

With ActiveSheet    
    lastRowF = .Range("E" & .Rows.Count).End(xlUp).Row
    .Range("F2:F" & lastRowF).FormulaR1C1 = "=DATEVALUE(RC[-1])"
    .Range("F2:F" & lastRowF).NumberFormat = "m/d/yyyy"
End with

End Sub

If you want to use A1 style:

Sub formula()

Dim lastRowF As Long

With ActiveSheet    
    lastRowF = .Range("E" & .Rows.Count).End(xlUp).Row
    .Range("F2:F" & lastRowF).Formula = "=DATEVALUE(E2)"
    .Range("F2:F" & lastRowF).NumberFormat = "m/d/yyyy"
End with

End Sub