Replacing Column with an Identical Column Inside a

Question

Is there any way in Excel to 'paste' a formula over a column so that each cell receiving the formula is taken as input in the formula? For example, I would like to write =Text() over a range without first putting Text(A1) over an empty column, dragging it down, and pasting the results into the original column. Is this possible? Thanks!

Answer 1

Here is an example that replaces a column of values with a column of formulas:

Sub Formulate()
    Dim N As Long, i As Long, dq As String
    N = Cells(Rows.Count, "A").End(xlUp).Row
    dq = Chr(34)
    For i = 1 To N
        v = Cells(i, 1).Value
        Cells(i, 1).Formula = "=Text(" & v & "," & dq & "mm/dd/yyyy" & dq & ")"
    Next i
End Sub