easy way to fill out a range with formulas?

[dreamz]

hi,

i have a spreadsheet that is set up like a matrix, names on the left,
different metrics along the top.

example:

Code:
--------------------
f and g are different functions with the name as argument (e.g., a lookup function)

name metric1 metric2
aaa f(aaa) g(aaa)
bbb f(bbb) g(bbb)
ccc f(ccc) g(ccc)
ddd f(ddd) g(ddd)
--------------------


now, i have the name column set up as a named range. i can write
something like:


Code:
--------------------
Range("name").Offset(0, 1).Value = "=f(a1)"
--------------------

in other words, explicitly write the function into the cell relative to
the named range, but this is inefficient when i have 60 or so columns to
fill out. is there an easy way to do this? i'm trying to avoid
copy/paste because it's slow.

i cannot keep the formulas in there because there are hundreds of lines
per sheet, and it needs to be clean (i.e. exactly as long as the named
range) for the task at hand.

thanks.

 

[Guest]

Writing the first formula in the top cell of each column and then simply
dragging it down so that it autofills the range should increment the cell
references. Alternatively, particularly if range "names" is dynamic, you
could use a macro like this:

Sub x()
Dim c As Range

For Each c In Range("names")
c(1, 2).Formula = "=f(" & c.Address(False, False, xlA1) & ")"
c(1, 3).Formula = "=g(" & c.Address(False, False, xlA1) & ")"
Next
End Sub

Regards,
Greg

 

[dreamz]

thanks for the reply.

i used to put the formula into the top cells and then copy down.
haven't tried filldown, but is it slow or inefficient? is it worse tha
trying to do something with the "value" method?

and the second one may not work as well, especially since i'd need t
write out all the formulas

 

[Guest]

The macro will fill the formulas into columns B and C essentially
instantaneously using a loop. You don't need to write in any formulas unless
I don't understand your situation. Example where functions f and g are very
simple UDF's:

Sub Test()
Dim c As Range
For Each c In Range("NamesRng")
c(1, 2).Formula = "=f(" & c.Address(False, False, xlA1) & ")"
c(1, 3).Formula = "=g(" & c.Address(False, False, xlA1) & ")"
Next
End Sub

Function f(txt As String) As String
f = Left(txt, 3) & Len(txt)
End Function

Function g(txt As String) As String
g = Len(txt) & Right(txt, 3)
End Function

The AutoFill method should only take seconds. Don't copy/paste. To AutoFill:
1. Select the top cell containing a formula in column B.
2. Move the mouse pointer over top of the little black square at the
bottom-right corner of the cell. The mouse pointer should convert to a plus
sign ("+").
3. Hold down the left mouse button and drag down till it fills the range.
The cell references should increment as required.

Regards,
Greg

 

[ward376]

You can use a line like this to fill a column in the current region
with a formula:

Cells(1, 1).CurrentRegion.Offset(1, 0).Columns(1).FormulaR1C1 =
"=yourfunctionandargument"

If you disable events and set calculation to manual, it will fill the
column quickly with formulas, but they still have to calculate at some
point.
If you have 60+ columns and hundreds of rows, you're calculating
thousands of formulas and it will take some time. I use this line in
some projects with less than half a dozen columns of formulas and
hundreds or thousands of rows and it takes several seconds just to
calculate.

 

[dreamz]

thanks for the explanation. if i understand you correctly, you still
define all the functions, e.g., here:


Code:
--------------------
Function f(txt As String) As String
f = Left(txt, 3) & Len(txt)
End Function

Function g(txt As String) As String
g = Len(txt) & Right(txt, 3)
End Function
--------------------


in my case, it's not feasible, as there are over 200, some complex,
formulas.

and about the autofill, i'm aware that i can autofill manually. i meant
"filldown" as in the method in vba. sorry if that wasn't clear. i don't
know if it's slower or less efficient than other methods, but i'll try
it.

 

[ward376]

AutoFill - once you have the mouse pointer over the fill handle (the
little black square in the bottom right corner of the active cell) you
can double-click and it will fill until it reaches an empty row.

 

[Guest]

I agree with Greg's suggestion to place the formulas in the first row. This
sounds to me like a perfect candidate for creating a template once, and
re-using it, modifying it as needed. This may sound like a lot of work to
you, but it's really the simplest way to go.

If you're not comfortable with autofill, I have some code here that will do
what you want. It requires that your spreadsheet be designed to work with it,
but you may modify it to suit. Here's how it works:

1. Set up the first row from B1 to however many columns you need with the
formula required for each column. The formula should be entered somewhat like
this:

=IF($A1<>"",YourFormulaGoesHere,"FormulaDescriptionGoesHere")

This will only put the formula in cells that have a name entered in
column A, and provide a heading across the first row as A1 will be empty.
There should be no empty rows in your table past the last name in the list,
or blanks in the list of names in column A.

2. Start your names list in A2, making sure A1 is empty. How you get the
list into the cells is up to you.

It doesn't matter how many columns or rows you have because the code adjusts
accordingly. It does matter that they are contiguous.

Here's the code:

Sub FillMyFormulas()
' Copies a set of formulas across columns and down rows.
' Requires the formulas being placed in the first row, starting in B1
' and names listed in column A starting in A2.

Dim lLastRow As Long
Dim iLastCol As Integer, i As Integer

With ActiveSheet
lLastRow = .Cells(.Rows.Count, 1).End(xlUp).Row
iLastCol = .Cells(1, .Columns.Count).End(xlToLeft).Column

For i = 2 To iLastCol
With Range(Cells(1, i), Cells(lLastRow, i))
.Formula = Cells(1, i).Formula
End With
Next i
End With

End Sub

HTH
Regards,
Garry

 

[Guest]

Ward372 has a better solution. I tried his approach but botched it. (I've
done this before myself but it's not my day). Changing it to accept the named
range (which could be dynamic) and trasnlating to A1 style and with a slight
condensation:

Range("NamesRng").Offset(0, 1).Formula = "=f(A1)"
Range("NamesRng").Offset(0, 2).Formula = "=g(A1)"

Note that these two simple lines of code will populate both columns B and C
with the required formula and will increment the cell references (A1, A2,
A3...).

He also showed both of us an excellent trick on autofilling of which I was
completely unaware. Thanks Ward372.

As for your question about defining the formula:
I used two simple User Defined Functions (UDF's) for my demo only. You don't
need to do this if you're using existing functions. These functions simply
accepted a single string (text) argument and did something with it. Therefore
my code demo actually worked. The functions f and g in your post both
accepted a single string argument and there are relatively few existing
functions that accept a single string argument. So I got the impression you
might be using UDF's yourself.

Regards,
Greg

 

[Guest]

Thanks,

See my post to dreamz.

Greg