0

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!

114
  • 876
  • 3
  • 25
  • 51

1 Answers1

2

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
Gary's Student
  • 95,722
  • 10
  • 59
  • 99
  • This is great, would you happen to be able to explain how to modify the formula being used? I am not very familiar with the " & v $" syntax used. What is dq. Thanks again! – 114 Jan 03 '14 at 21:32
  • @Stopwatch: the **&** is a concatenation of pieces of strings. the **dq** is an in-line double-quote. – Gary's Student Jan 04 '14 at 00:02
  • Thanks! So would it be much the same to modify the code and have it use the =Sum function, for example? – 114 Jan 04 '14 at 20:23
  • As an example, =SUM(A1,B3,C6). – 114 Jan 05 '14 at 01:16